Create Read-Only Database User in SQL Server

The following describes how to setup a database user with read-only access to the AdventureWorks database.

Using SQL Server Management Studio 2008:
  1. Connect to your database server.
  2. Expand Security > Logins.
  3. Right-click on the user who will be set as having read-only access (in this example "adventureworksro").
  4. Select Properties.
  5. Select User Mapping.
  6. Map the login to the database they will have access to.
  7. Tick the boxes for role membership next to public and db_datareader.
  8. Confirm by clicking OK.
You should get something like the following:
Login Properties - Adventureworksro


Database-level role names

from http://msdn.microsoft.com/en-us/library/ms189121(SQL.100).aspx

db_accessadmin

Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator

Members of the db_backupoperator fixed database role can back up the database.

db_datareader

Members of the db_datareader fixed database role can read all data from all user tables.

db_datawriter

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_ddladmin

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_denydatareader

Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

db_denydatawriter

Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_owner

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

db_securityadmin

Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.


In theory, a user who can do nearly everything but modify access and security permissions:
Database Role Membership - All but Security

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.