It is easy to create a read only user in MS SQL Server Management Studio. When you create your user, simply assign the following roles:
Here are the steps.
- From MS SQL Server Management Studio, connect to your database.
- Navigate to Security > Logins and then right-click on it to create new user.
- On the General page, fill all the required fields.
- On the User Mapping page, assign your new user to a database. Then, check db_datareader role.
Test read-only user
Execute the following
-- Adapt the statement according to your case. UPDATE TABLENAME SET COLUMNNAME=XX WHERE ID=XX;
It should return the following error message:
The UPDATE permission was denied on the object 'TABLENAME', database 'DATABASENAME', schema 'dbo'.