Read-Only access to MSSQL Database – using SQL Queries

Some times it is required you to share some database on MSSQL server to users with read-only access. This is very easy and can be setup in 2 minutes only. The concept is very simple you just have to create an user on master database, and then assign database to that user with db_datareader role. So below are the steps you have to follow with query.

Run These on master Database

CREATE LOGIN user_read WITH PASSWORD = 'PocoF1@321';
GO

Run These on Client Database

CREATE USER user_read FOR LOGIN user_read;
GO
EXEC sp_addrolemember db_datareader, 'user_read';

If you want to remove any database access from this user then run below query

EXEC sp_droprolemember db_datareader, 'user_read';

I think you have find how easy it was. So stay tuned for more such kind of tips and articles.

Thanks