An overview of the db_datareader role

In this article, we are going to learn about the db_datareader role. It is a fixed, database-level
role. The database-level roles are a group of security principals that are used to manage the permissions within the
databases more efficiently. There are two types of database-level roles. One is a predefined database-level role,
and another is custom database-level roles. The custom database-level roles can be created by the database
administrator or the member of the db_owner role.

The SQL Server database has eleven fixed database roles. Among these eleven roles, the dbmanager and Loginmanager
are a special role for the Azure SQL database.

  1. db_backupoperator: The members of the database role db_backupoperator role can
    take the backup of the database
  2. db_datareader: This role gives an ability to read the data from any table of the database
  3. db_datawriter: This role gives an ability to write the data in the table of the database. When
    we grant this role to the user, it can insert the data, but it cannot read, change, or delete it
  4. db_ddladmin: This role gives an ability to perform any DDL statement on the database. The
    members of this role can create database objects (tables, stored procedure, views, etc.)
  5. db_denydatareader: When a user is assigned a db_denydatareader role, it cannot
    read the data from the table of the database
  6. db_denydatawriter: When a user is assigned a db_denydatawriter role, it cannot
    insert the data to any table of the database
  7. db_owner: This role gives an ability to perform all configuration and maintenance activities on
    the database. When a user is assigned a db_owner role, it can create or drop the database object, generate the
    backup, configure the security, and perform maintenance task (consistency check, index, and statistics
    maintenance)
  8. db_accessadmin: When a user is assigned the db_accessadmin role, it can grant
    or revoke the access of any SQL Login, Windows logins, or Windows groups. The members of the db_accessadmin role
    can run any of the following procedures

    1. sp_dropuser
    2. sp_adduser
    3. sp_revokedbaccess
    4. sp_grantdbaccess
  9. db_securityadmin: When a user is assigned a db_securityadmin role, it can
    grant or revoke the permissions of the user in the database. The members of the db_securityadmin role cannot
    create the users, but they can create roles and assign them to the users that have access to the database. The
    members of the db_accessadmin role can run any of the following procedures and commands

    1. DENY
    2. GRANT
    3. REVOKE
    4. Sp_addapprole
    5. Sp_addrole
    6. Sp_addrolemember
    7. Sp_approlepassword
    8. Sp_changeobjectowner
    9. Sp_droprolemember
    10. Sp_droprole
    11. Sp_dropapprole
  10. dbmanager: This role applies to the Azure SQL Database. This role gives an ability to create
    the database and become the owner of the database that allows the user to connect as a dbo user. The dbo user
    has all the database permission on the database
  11. loginmanager: This role applies to the Azure SQL Database. The member of the Loginmanager role
    can create or delete the logins in the master database

Add new SQL Login to the role

In the first example, let us see how we can create a SQL login and add it to the db_datareader
role. First, we will create a new user. To do that, open SQL Server management studio Connect to SQL Server instance
using the Administrator account Expand Security Right-click on Logins and select New Login.

New Login

On the General tab, enter the desired username in the Username text box. If you want to use an existing domain user,
then select Windows Authentication, or you can select SQL Server authentication.
Enter the desired password in Password and Confirm password text boxes.

New Login details

On the User Mapping screen, select the name of the database. When you click on the database, the
list of the fixed database roles enables in the Database role membership for the list box.
Click on the db_datareader and Click OK to close the dialog box.

Assign role to Login

Connect to SQL Server instance using testuser account and execute the following query:

Output:

Run Select query

As you can see, we can execute the SELECT query.

Add existing SQL Login to the role

Let us see how we can add the existing SQL Login to the db_datareader role. To demonstrate, I have
created a user named nisargupadhyay on the SQL Server instance. The Public role is
assigned on AdventureWorks2017 to the user. Let us run the SELECT query on the database.

You will receive the following error

Msg 229, Level 14, State 5, Line 3

The SELECT permission was denied on the object ‘Vendor’, database ‘AdventureWorks2017’, schema ‘Purchasing’.

Output:

Select Query error

Now, let us add the nisargupadhyay login to the db_datareader role. To do that, expand
Security Expand Logins Right-click on nisargupadhyay
Select Properties.

View Logins

In the properties dialog box, click on User Mapping Select AdventureWorks2017
Click on db_datareader role and click OK.

Login Properties

Execute the SELECT query again

The output is as follows:

Select query executed successfully

Summary

In this article, I have given an overview of the various database-level roles. I have specifically explained the
db_datareader role and its usage. Moreover, I have demonstrated how we can add the new SQL Login or
existing SQL Login to the database role using SQL Server Management Studio.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)

Author: admin

Leave a Reply

Your email address will not be published.