Create a new db user in SQL Server 2005

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.

You'll have to create it first as a user, and then set up the correct permissions for the user.

1. you'll have to ensure that your DB is configured with both User auth and SQL auth If using the Management Studio: right-click on the Server, select "Security" ensure that server authentication is "SQL Server and Windows Authentication mode"

2. in Security-logins, right click and select "New Login", select SQL Authentication, use the username and password you like.

   USE [master]
   GO
   CREATE LOGIN [ test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[MY_DATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   GO 
   

3. on the DB you want, in security, users, select new User. Select a username, and attach the login name you've just created, and select the roles you want to apply to this user (i.e. db_datareader, db_datawriter):

   USE [MY_DATABASE]
   GO
   CREATE USER [myDefaultUser] FOR LOGIN [ test]
   GO
   USE [MY_DATABASE]
   GO
   EXEC sp_addrolemember N'db_datareader', N'myDefaultUser'
   GO
   USE [MY_DATABASE]
   GO
   EXEC sp_addrolemember N'db_datawriter', N'myDefaultUser'
   GO
   

That is it. Now you can create your connection string using this password.

CREATE LOGIN MyNewUser WITH PASSWORD = 'top$secret'

USE AdventureWorks 
CREATE USER MyNewUser FOR LOGIN MyNewUser 
GO

USE [MASTER]


EXEC master.dbo.sp_addlogin @loginame = N'USERNAME', @passwd = 'THEPASS' @defdb = N'master', @deflanguage = N'us_english'


USE [YOUR_DB]
    EXEC dbo.sp_grantdbaccess @loginame = N'USERNAME', @name_in_db = N'USERNAME'