Understanding SQL Server 2008 R2 Fixed Database Level Roles

The db_denydatareader fixed database role.

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

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

This creates a server level login named denydatareader. 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_denydatareader 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 denydatareader present in the fixeddatabaseroles have db_owner and db_denydatareader fixed database roles.

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

User Name: denydatareader

Password: P@ssw0rd

Now consider a case where a user having db_denydatareader tries to read the data from the user table named STUDENT which is present in fixeddatabaseroles database, please refer the screen capture below:

As seen from the above screen capture we can conclude that the user having denydatareader fixed database role is unable to read data from the user table named STUDENT.

Consider another case where the user having denydatareader fixed database role tries to UPDATE the 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 denydatareader fixed database role has been denied to UPDATE the record in the user table.

Consider another case where the user having denydatareader fixed database role tries to delete the data from the user table named STUDENT, please refer the screen capture below:

From the above screen capture we can conclude that the user having denydatareader fixed database role can DELETE the data present in the user table which is absolutely true as per the definition which says that user having denydatareader fixed database role cannot read the data from the user table.

Continues…

Pages: 1 2 3 4




Array

No comments yet... Be the first to leave a reply!