Understanding SQL Server 2008 R2 Fixed Database Level Roles

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:










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





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

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


Pages: 1 2 3 4


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