SQL Server Database Roles

[Previous] [Next]

Each SQL Server database can contain roles. Roles are similar to Windows groups, except that they are strictly internal to a particular database, unlike fixed server roles. There are three kinds of roles in a SQL Server database: fixed, user, and application. To add a user to a database role, run the sp_addrolemember command. For instance, to add EXAIR\Michael to the database role db_owner, you would run

 exec sp_addrolemember 'db_owner', [EXAIR\Michael] 

Role membership takes effect immediately; there's no need to restart SQL Server.

Fixed Database Roles

Fixed database roles are similar to fixed server roles—they provide a mechanism for convenient built-in groupings of rights to SQL Server users. There are nine fixed database roles, which the following sections describe. Unlike fixed server roles, members of each role don't have the ability to add other users to their roles, except where noted.

db_owner

Members of the db_owner role are, for the most part, equal to the database owner (that is, the dbo user). There are a few minor tasks that a dbo can do that a db_owner cannot—most of these commands are for backward compatibility with SQL Server 6.x. Otherwise, db_owner members can do any operation within a single database.

db_accessadmin

Members of the db_accessadmin role can grant and revoke access to the database.

db_securityadmin

Members of the db_securityadmin role are the other half of db_accessadmin role members from a security perspective—they can assign permissions (the grant, revoke, and deny statements examined below) to users and roles within the database. They can also fully administer user-defined database roles and change ownership of database objects.

db_backupoperator

Members of the db_backupoperator role can back up a database and the transaction log as well as issue a CHECKPOINT command within the database.

db_ddladmin

Members of the db_ddladmin role can issue any data definition language (DDL) statements, such as CREATE TABLE, CREATE VIEW, and so on. They can also set full-text properties on tables and view index statistics.

db_datareader

The db_datareader role is a shortcut to grant select (read) permissions on all tables and views within a database.

db_datawriter

The db_datawriter role is a shortcut to grant insert, update, and delete permissions on all tables and views within a database.

db_denydatareader

Members of the db_denydatareader role are unable to select (read) any table or view in the database. Since the role is a deny role, it overrides any other rights you have granted. This role, along with db_denydatawriter, can be used when combined with db_backupoperator to give an operator the right to back up a database without being able to view the data within the database.

db_denydatawriter

The db_denydatawriter role prevents the user from issuing any insert, update, or delete commands against any table or view within the database.

User Database Roles

Besides the fixed database roles that come built in to each database, you're able to create your own roles in a database. You do this with the sp_addrole system stored procedure. For instance, to create a role named Payroll_Users, you would run

 exec sp_addrole 'Payroll_Users' 

This would add a new row to sysusers for the Payroll_Users role and assign it a SQL Server internal-use only SID (really a GUID, just as for SQL Server security logins).

To add a user to the role, use sp_addrolemember. To add Michael to this role, you would run

 exec sp_addrolemember 'Payroll_Users', [EXAIR\Michael] 

This adds a row to the sysmembers system table, reflecting Michael's membership in this role.

To drop the role from the database, you would run sp_droprole:

 exec sp_droprole 'Payroll_Users' 

Once this command is executed, the row in sysusers is deleted. All users who were in the role are simply removed from the role as it's being dropped.

The "Public" Role

Although, strictly speaking, Public isn't really a role, it's important to note it somewhere. Public is the equivalent of the Everyone group in Windows NT or Windows 2000. You can't remove someone from Public, and any grants to Public apply to everyone within the database. For example, if you deny Public insert permissions on a table, no one will be able to insert into that table, because "everyone" includes you! This is one of the frequent complaints of new security-conscious administrators.

Application Roles

Application roles group users together in a sense, like user-defined database roles; however, there is no such thing as membership in an Application role. Additionally, a password is assigned to an Application role. You create an Application role with the sp_addapprole system stored procedure. Application roles are also just entries in the sysusers table. When you add an application role, you must assign a password. For instance, to create a role named Payroll, you would run

 exec sp_addapprole 'Payroll','MyHard$password@' 

Where things veer off track is the way that Application roles are used. Ordinary roles simply take effect when you use a database. Application roles must be explicitly enabled. This is done with the sp_setapprole command:

 exec sp_setapprole 'Payroll', {Encrypt N'MyHard$passw0rd@#} 

When the Application role is enabled, all other permissions for the user are turned off within the database and only the permissions that have been specifically assigned to the role are enabled. The exception is that rights assigned to Public are still in effect, because the Application role is a member of the Public role.

The only way to turn off an Application role is to terminate your session with SQL Server.

So, why is this useful? Let's assume you've created a payroll application. You want to assign your users integrated Windows security, and you want them to be able to log in as themselves and switch to the payroll database when running your application. What you don't want them to be able to do is access payroll data outside of your application. So you add a login for the Windows group EXAIR\Payroll_Admins and add the group as a user to the payroll database. However, you don't assign any rights or role membership to the users. You grant all rights to the Payroll application role. Then, your application enables the application role with a call to sp_setapprole. All other rights are turned off in that SQL Server database, and your application essentially has the rights instead of the users having the rights directly. All activity is still audited with the user's real Windows login names, so you'll know who authorized that 15 percent raise but didn't share with you.