Database roles are used to assign permissions to users within a database. Without the use of roles, you would have to assign permissions to users on an individual basis. With the use of database roles you can assign permissions to the individual role and then assign users to that role. This greatly simplifies the process of assigning security permissions to users in the database.
There are two main types of database level roles which include Fixed Database Level Roles and Flexible Database Level Roles. Flexible roles are essentially custom roles that can be created if there are no fixed roles that provide sufficient permissions for a particular user or group of users. Flexible roles can be created by any user assigned to the
CREATE ROLE permission or a member of the
db_securityadmin fixed database role.
For the purposes of this article I will be providing an overview of the Fixed Database Level Roles as I am routinely asked by both developers and analysts to clarify the permissions granted by various fixed roles within SQL Server.
To review an individual user’s database roles for an particular database simply connect to your SQL Server, locate the applicable database in the Databases drop-down and then expand that database’s drop-down and locate the Security option. Expand Security, expand Logins and locate the user’s login that you want to review. Right-click the login and select Properties.
Once the Database User window is displayed, click on the Membership page to review the list of available database roles.
As you can see from the above screenshot, I am currently assigned to
db_owner. By default, most of our users are assigned the
db_writer role. However, if you are assigned to db_owner, it is not necessary to be assigned to any other roles as you can essentially perform all tasks on the database.
To better explain the various functions of each fixed database level role I have provided an overview of each role below:
This role is assigned to security principals who need to be able to manage database access for user logins.
This role allows members to back up the database.
Members of this role can read/
SELECT all data from all user tables in the database.
Allows members to
DELETE, or otherwise modify data in all user tables in the database.
Members of this role can run any Data Definition Language (DDL) command in the database.
All members of this role are blocked from reading data in all database user tables.
All members of this role are blocked from inserting, deleting or otherwise modifying data in all database user tables.
This role allows its members to perform all database configuration and management tasks, including dropping of the database. This role should be limited to Database Administrators only.
This role allows its members to manage the membership of fixed and flexible database roles. Members of this role can essentially modify their own security to that of db_owner. This role should only be assigned when absolutely necessary.
Another method for adding users to a particular database role is to add them directly to the role from within the role’s properties window, as opposed to adding the role to the user through their login properties as previously discussed.
To do this locate the individual database, expand the Security drop-down and then expand Roles and Database Roles. Here you will see the familiar list of fixed database roles.
Right-click a particular role to view it’s properties. At the bottom of the screen you will see a list of all users assigned to this role. To add additional users, just click the Add… button and provide the necessary account information.
Additionally you can add users to roles by scripting it out. The script below demonstrates adding a user (Jworthen) to the
db_datawriter roles on the AdventureWorks2012 database.
ALTER ROLE [db_datareader] ADD MEMBER [Jworthen]
ALTER ROLE [db_datawriter] ADD MEMBER [JWorthen]
If you’re working in a testing database it may help you better understand the various functions of each role by assigning them to yourself and attempting to perform various functions within the database. Take note of the error messages that you encounter as they will help you better understand the relationship between the fixed roles and the permissions they grant.
You could also utilize the
EXECUTE AS to impersonate a particular login when testing. The following script will allow you to execute SQL commands as another user:
EXECUTE AS USER='TestUser' --insert login here
--insert SQL here
Using this method may be easier when comparing certain role permissions as it will prevent you from downgrading your own permissions to a point where you would then have to log in as ‘sa’ to restore them in order to continue modifying your permissions.