Understanding SQL Server 2008 R2 Fixed Database Level Roles

To easily manage the permissions in our databases, Microsoft has provided several roles in SQL Server which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope. The various types of SQL Server fixed database roles are as follows:

· Db_owner

· Db_securityadmin

· Db_accessadmin

· Db_backupoperator

· Db_ddladmin

· Db_datareader

· Db_datawriter

· Db_denydatareader

· Db_denydatawriter

I have divided the article into a two-part series. In part one, I will be explaining the below four fixed database roles:

· Db_datareader

· Db_datawriter

· Db_denydatareader

· Db_denydatawriter

In order to understand the SQL Server fixed database role, let us create a database named fixeddatabaseroles. The syntax for the same is as shown in the screen capture below:

The db_datareader fixed database role

Members belonging to db_datareader fixed database role can read all the data from the user tables. In order to understand db_datareader fixed database role, let us create a server level login named datareader having public fixed server role and we will provide it access to the fixeddatabaseroles database with just db_datareader rights.

In order to create the server level login named datareader, execute the T-SQL script as shown in the screen capture below:

This creates a server level login named datareader. In order to determine whether the login has been created or not just expand the Logins node present under the Security node in the SQL Server Management Studio.

In order to provide the above login access to the database fixeddatabaseroles execute the below T-SQL script against the fixeddatabaseroles database.

CREATE USER datareader for LOGIN datareader

This creates a login named datareader which has access to the fixeddatabaseroles database.

After mapping the login to the database, we then need to assign the login db_owner and db_datareader fixed database roles, please refer the screen capture below which contains the T-SQL scripts to achieve the goal.

Once the above script gets executed, the user named datareader having access to fixeddatabaseroles database gets db_owner and db_datareader fixed database role, please refer the screen capture below.

Now consider a case where the user named datareader tries to create a table named student in the fixeddatabaseroles database. In order to do so, connect to the SQL Server Management Studio using datareader login credentials.

User Name: datareader

Password: P@ssw0rd

Continues…

Pages: 1 2 3 4




Related Articles :

6 Responses to “Understanding SQL Server 2008 R2 Fixed Database Level Roles”

  1. This is an excellent article. I appreciate the effort in elaborating the relationships between server roles and database roles and categorizing them. It would be much more better if it were a little more explanatory in terms of comparisons.
    Bottom line, excellent work !!

  2. Great job.
    Thanks a lot !

  3. datawriter role needs a datareader role in order to delete a data with WHERE clause.

    example
    “delete from students where student_ID = ’1′”

    needs both datareader and datawriter roles.

  4. This is awesome!! really helpful for me. Thanks for sharing with us. Following links also helped me to complete my task.

    http://msdn.microsoft.com/en-us/library/aa337562(v=sql.105).aspx

    http://www.mindstick.com/Blog/401/Create%20User%20Login%20in%20SQL%20Server%202008%20R2

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |