Understanding SQL Server 2008 R2 Fixed Database Level Roles

The db_denydatawriter fixed database role.

Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database. In order to understand db_denydatawriter fixed database role, let us create a server level login named denydatawriter having public fixed server role and we will provide it access to the fixeddatabaseroles database with just db_owner and db_denydatawriter rights.

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

This creates a server level login named denydatawriter. 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, please refer the screen capture below.

Now we need to map the above login to fixeddatabaseroles database. In order to do it, execute the below T-SQL script against the fixeddatabaseroles database.

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

Please refer the screen capture below which shows that the user named denydatawriter present in the fixeddatabaseroles have db_owner and db_denydatawriter fixed database roles.

Connect to the SQL Server Management Studio using denydatawriter login credentials.

User Name: denydatawriter

Password: P@ssw0rd

Now consider a case where the user having denydatawriter fixed database role tries to INSERT data in the user table named STUDENT, please refer the screen capture below:

As seen from the above screen capture we can conclude that the user having denydatawriter fixed database role cannot add the data into the user table.

Consider another case wherein a user having denydatawriter fixed database role tries to UPDATE the data present in the user table named STUDENT, please refer the screen capture below:

From the above screen capture we can conclude that the user having denydatawriter fixed database role cannot MODIFY the data in the user table.

Consider another case wherein a user having denydatawriter fixed database role tries to DELETE the data present in the user table named STUDENT, please refer the screen capture below:

From the above screen capture we can conclude that the user having denydatawriter fixed database role cannot DELETE the data present in the user table.

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 |