Thursday, October 30, 2014
   
Text Size
Login

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

Comments   

Ramu
# Ramu Thu, 10th November 2011
Excellent i got more points..
Like | Dislike | +1 Reply | Reply with quote | Quote
Ecommerce Designer
# Ecommerce Sat, 14th January 2012
That’s such a great post!
Like | Dislike | 0 Reply | Reply with quote | Quote
Hadoop map
# Hadoop Wed, 18th January 2012
Excellent post.. Keep it up.
Like | Dislike | 0 Reply | Reply with quote | Quote
krishna leo
# krishna Thu, 18th April 2013
:D wow it works grat
Like | Dislike | 0 Reply | Reply with quote | Quote
Coverskin
# Coverskin Mon, 30th September 2013
Thank you very much for posting and sharing this great article. It is so interesting. I want to know some other information about this site. So please give me this news quickly. I always will be aware of you.
Like | Dislike | 0 Reply | Reply with quote | Quote
fely
# fely Fri, 25th October 2013
I created a new login and a new user and I mapped a database in SQL Server 2012. In the login properties in Securables, we selected permissions "deny" for "View any database" for the user to see only the specified database.

The problem is that the user does not see any database.

If I select "Grant" to "View any database", user can see all databases. How can I make the user can see only the specified database?
Like | Dislike | +1 Reply | Reply with quote | Quote
# Webmaster Fri, 25th October 2013
Hi Fely,

I'm so jealous as we're still on SQL Server 2008 R2. I'm also not 100% sure if the problem happens in v2012 for security reasons. I figure you add users to individual databases and not to the overall database server. The creating user will more than often have db_owner privileges. I can only suggest checking the other databases that this user is not allowed to access and set the db_denydataread er for them...
Like | Dislike | 0 Reply | Reply with quote | Quote
Martin Kornet
# Martin Wed, 4th June 2014
Thnx voor the explanation great!
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Joes Quicklist Weblinks (JQW)

    • Mon 27-Oct-14
      Hi. I was able to download today with my laptop in a different location. Both computers using Ubuntu ...
      Webcrawler  
    • Sun 26-Oct-14
      Hmm... thanks Webcrawler! I can't replicate the error as all the links work for both my superuser ...
      Webmaster  
    • Sun 26-Oct-14
      Can't download new version. I get the same error message as Kat a few post's up.... CTRL+F5 does not ...
      Webcrawler  
    • Sat 25-Oct-14
      Hi Traveller, Apologies for the incredible delay as I have been focusing on another app in development.
      Webmaster
  • SSIS Script: convert UPPERCASE to Mixed-Case using TitleCase

    • Tue 28-Oct-14
      This post was immensely helpful, thank you. I used the code for the script component and made some ...
      Dave L .