Understanding SQL Server 2008 R2 Fixed Database Level Roles

From the below screen capture we can conclude that as soon as the user named datareader having db_datareader fixed database role tries to create a table named student in the fixeddatabaseroles database, he has been denied from doing so.

Consider another case where the user having db_datareader fixed database role tries to read the data from the table named sysobjects, please refer the screen capture below:

Thus we can conclude that the user having db_datareader fixed database role can only perform the read operations. Apart from that all other operations will not be allowed to him.


The db_datawriter fixed database role

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. In order to understand db_datawriter fixed database role, let us create a server level login named datawriter having public fixed server role and we will provide it access to the fixeddatabaseroles database with just db_datawriter rights.

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

This creates a server level login named datawriter. 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 datawriter for LOGIN datawriter

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

In order to provide it db_datawriter fixed database role, execute the below T-SQL script against the fixeddatabaseroles database.

Once the above script gets executed, the user named datawriter having access to fixeddatabaseroles database gets db_datawriter fixed database role, please refer the screen capture below.

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

User Name: datawriter

Password: P@ssw0rd

From the below screen capture we can conclude that as soon as the user named datawriter having db_datawriter fixed database role tries to create a table named student in the fixeddatabaseroles database, he has been denied from doing so.

For understanding purpose, I connected to SSMS using sa credentials and created a table named student. Then I again connected to SSMS using datawriter credentials and tried to perform a SELECT operation on the student table and found that logins having datawriter fixed server role are not allowed to perform SELECT operation on the user table whereas if they try the same on system tables, they will be able to perform it, please refer the screen capture below.

Login having datawriter fixed database role tries to perform SELECT operation on the student table and was DENIED from doing so.

Login having datawriter fixed database role tries to perform SELECT operation on the system table named SYSOBJECTS and SUCCEEDED.

Consider another case when the login having datawriter fixed database role tries to INSERT the data into the student table.

From the above screen capture, we can see that the login having datawriter fixed database role can perform INSERT operation on the user table.

Consider another case when it tries to UPDATE the data in STUDENT table.

As seen from the above screen capture, we can conclude that the login having db_datawriter fixed database role can perform an UPDATE operation on the user table.

Consider another case where a login having datawriter fixed database role tries to perform a delete operation on the user table, please refer the screen capture below.

As seen from the above screen capture we can conclude that the login having datawriter fixed database role can DELETE data from the user table.

Consider another case where the login having datawriter fixed database role tries to create a server level login named test, please refer the screen capture below:

As seen from the above screen capture we can conclude that login having datawriter fixed database role cannot create a server level login.

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 |