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.

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.

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.

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

Output:

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:

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.

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

Execute the SELECT query again

The output is as follows:

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.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022