The most common SQL Server Connection for development environments in .NET are Data Source=localhost; Database=your_test_db; Trusted_Connection=True. Notice the Trusted_Connection is set to True and a username and password is not supplied. This is because it uses Windows Authentication.

At times this is not enough, and we need a SQL Server login. This is generally true for other environments such as test and production.

Below I breakdown the various SQL commands in order to create a SQL server login and grant necessary access.

Summary

In order to create a SQL Server login for a particular database, it can be broken down into three areas which I will cover below.

  1. Create a SQL Server login
  2. Create a SQL User associated with the login created for a specific database
  3. Grant necessary permissions for the SQL Server user such as readonly.

TLDR; You can jump to the final SQL Script to find the SQL script.

1. Create a SQL Server Login

First, we need to create a login. A login in SQL Server only allows access to the server.

In the example below, we are going to create a login for local_dev_user with password azNSiLKN1VnFQDuC.

USE [master]
CREATE LOGIN local_dev_user WITH PASSWORD = 'azNSiLKN1VnFQDuC'; -- set your own pwd here

Notice, the use of the master database statement in the first line. This is required so that a login is created against the master database.

2. Create a user for a specific database

Next, we need to create a user associated to the login we created in step 1 and also for a particular database.

USE [your_test_db] -- Set your database here

-- Creates a database user for the login created above.  
CREATE USER local_dev_user FOR LOGIN local_dev_user

GO

Here, notice that we have set the desired database that the login is needed with the first use statement.

3. Grant permissions

Now, we can grant permissions and add the relevant roles for the user.

EXEC sp_addrolemember 'db_datareader', 'local_dev_user';
EXEC sp_addrolemember 'db_datawriter', 'local_dev_user';
EXEC sp_addrolemember 'db_owner', 'local_dev_user';

Here, I am setting the user to be a reader, writer and an owner.

The final SQL Script

Here is the final SQL script for reference.

USE [master]
CREATE LOGIN local_dev_user WITH PASSWORD = 'azNSiLKN1VnFQDuC'; -- set your own pwd here  

USE [your_test_db] -- Set your database here
-- Creates a database user for the login created above.  
CREATE USER local_dev_user FOR LOGIN local_dev_user
GO

EXEC sp_addrolemember 'db_datareader', 'local_dev_user';
EXEC sp_addrolemember 'db_datawriter', 'local_dev_user';
EXEC sp_addrolemember 'db_owner', 'local_dev_user';

The SQL Server connection string

Finally, the SQL connection string for a .NET app now needs to be modified.

"Data Source=localhost; Database=your_test_db; User ID=local_dev_user;Password=azNSiLKN1VnFQDuC; Encrypt=true; TrustServerCertificate=True"

Also, be sure to check the SQL Server authentication mode is set to both “SQL Server and Windows Authentication mode”. This can be done by right-clicking on the server in SQL Management Studio and selecting “Properties”. In the Server Properties dialog, under “Security”, you can check the authentication mode enabled:

sql-server-mixed-auth