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.