Create a new db user in SQL Server 2005

CREATE LOGIN [user] WITH PASSWORD='password',        DEFAULT_DATABASE=[your_db], CHECK_POLICY=OFFGOCREATE USER [user] FOR LOGIN [user]EXEC sp_addrolemember N'db_datareader', N'your_db'EXEC sp_addrolemember N'db_datawriter', N'your_db'GO

Where CHECK_POLICY=OFF switches off password complexity check, etc

As of SQL Server 2005, you should basically create users in two steps:

• create a "login" to your SQL Server as a whole
• create users for this login in each database needed

You'd go about doing this like so:

CREATE LOGIN MyNewUser WITH PASSWORD = 'top$secret';

And the "USE" your database and create a user for that login:

USE AdventureWorks;CREATE USER MyNewUser FOR LOGIN MyNewUser

As indicated, use the CREATE LOGIN to create the ability to connect to SQL Server as that account. Then use CREATE USER within the database to give that login the ability to access the database in question.

However, a few security points based on some of these comments:

• If at all possible, you want to use Windows authentication, not a SQL Server based login (which is what you are doing when you use user/pwd in this manner). If you are running from a computer on the same domain as SQL Server, you can use a service account that is a Windows user account. This ensures the domain is the single source for security.
• You didn't say what rights the user needed. Avoid using db_datareader and db_datawriter roles whenever possible. They give IMPLICIT access to tables and views and if someone is performing a quick permissions check on the database, they may not think to check the membership in these roles. That means your reporting on security is using. Best practices say to create your own database role, assign permissions to it, and make the user a member of that role.
• Whenever possible, use a strong password. One example had the password policies turned off. SQL Server will use the password policy from the local server (which is usually set at the domain level). You want to maintain that strong password policy, if possible.