How to Create a Read-Only User in MsSQL Server - A Step-by-Step Guide

Creating a read-only user in SQL Server (MSSQL) is a common requirement when you need to give users access to view data without the ability to modify, delete, or insert any records. This ensures that users can generate reports, perform queries, and analyze data, but without risking any accidental changes to the database.

In this blog post, we will walk you through the process of creating a read-only user in SQL Server, including best practices and key concepts.

• Data Integrity: Prevents unauthorized modifications to the database.
• Security: Protects sensitive data from being accidentally or intentionally altered.
• Auditing/Reporting: Allows business users or analysts to generate reports and queries without compromising the database.

Step 1: Login to SQL Service 

First you need to login using your own MSSQL Database User who has the permission to create a login user and grant permissions. Generally MSSQL has default Administrator user called 'sa'. After login if you want to view all the existing users in SQL Server then run the following query. 

SELECT * FROM sys.server_principals;

Step 2: Create a SQL Server Login

A login in SQL Server is used to authenticate a user to the server instance. In this first step, we’ll create a login that will be associated with the user account.

Run this SQL script to create a new login:

CREATE LOGIN readOnlyUser 
WITH PASSWORD = 'StrongPassword123', 
     CHECK_POLICY = OFF, 
     CHECK_EXPIRATION = OFF;

Step 3: Create a User in the Database

Once the login is created, the next step is to create a user inside the specific database where the read-only access will be granted. This user will be mapped to the login we just created. First create the database where you want the user to have read-only access then create a user in database. 

USE [YourDatabaseName];
CREATE USER readOnlyUser FOR LOGIN readOnlyUser; 

SELECT name AS RoleName, 
       type_desc AS RoleType 
FROM sys.database_principals 
WHERE type IN ('R', 'X')  -- 'R' = Role, 'X' = External role
ORDER BY RoleName;

Run the following query to grant the read-only role (db_datareader) to user.

ALTER ROLE db_datareader ADD MEMBER readOnlyUser;

Step 5: Verify the User's Permissions

To confirm that the user has the correct permissions, you can role and permissions assigned to the user using the following query: 

SELECT dp.name AS DatabaseUser,
       dp.type_desc AS UserType,
       dr.name AS RoleName
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
WHERE dp.name = 'readOnlyUser';

Step 6: Test the Read-Only User

Finally, it’s a good idea to test the permissions of your new read-only user. Log in to the database using the readOnlyUser credentials and try to perform the following actions:

• SELECT queries: These should work fine, and the user should be able to retrieve data from any table or view.
• INSERT, UPDATE, DELETE, or ALTER: These should fail with an error, confirming that the user only has read-only access.