MS SQL - How to add read only user?

By xngo on October 16, 2019

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:

  • db_datareader
  • public

Here are the steps.

  1. From MS SQL Server Management Studio, connect to your database.
  2. Navigate to Security > Logins and then right-click on it to create new user.
    MS SQL Server Studio - Login path
  3. On the General page, fill all the required fields.
    MS SQL Server Studio - Login > General page
  4. On the User Mapping page, assign your new user to a database. Then, check db_datareader role.
    MS SQL Server Studio - Login > User Mapping page

Test read-only user

Execute the following UPDATE statement.

-- Adapt the statement according to your case.

It should return the following error message:

The UPDATE permission was denied on the object 'TABLENAME', database 'DATABASENAME', schema 'dbo'.

About the author

Xuan Ngo is the founder of He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.