SQL Server Roles

An SQL server role corresponds to a set of database and data object access permissions assigned to operators. SQL Server supports server roles, database roles and application roles. Server roles are common across the entire server installation and not specific to any single database. The number and type of server roles are fixed by the system; new server roles may not be added. However, new or existing users may be assigned to any server role. The following is a list of server roles and descriptions. You may view these roles by expanding the main server security folder and the 'Server Roles' folder under this folder.

Role	Description
bulkadmin	Bulk inserts and other related operations
dbcreator	Create, modify, and resize databases
diskadmin	Manage disk files
processadmin	Manage SQL Server processes
securityadmin	Create and manage server logins, perform audits, read error logs
serveradmin	Change server configuration, shut down server
setupadmin	Manage linked servers, replication, stored procedures, and execute certain system stored procedures
sysadmin	Broad access and complete control over all database functions (DBA)

You may add users to roles by using the Management Server GUI or by running special System stored procedures on the command line within a new query window. To assign a server role to a user through the GUI, right click on the role (expand the main folder for security -> Server Roles) and click 'properties'. Click 'Add' to open the 'select login' dialogue box. Here, you may click the browse button to view available logins. Check the box next to a login and click OK to add the login to the selected role.

Add Logins to Role

Alternatively, you may execute a system stored procedure on a query window to add a user to a role. The sp_addsrvrolemember procedure may be used for this purpose:

Add User to Role:
sp_addsrvrolemember <login_name>, <role_name>
Drop User from Role:
sp_dropsrvrolemember <login_name>, <role_name>

SQL Server supports certain fixed database roles, a public database role, and user-defined database roles. Database roles are relevant to each database and establish access permissions at the database level. You may view the fixed roles for a database by expanding the 'Security' folder under the database. Fixed roles are defined by the system; a DBA may add new users to existing fixed roles. The following are some fixed roles and their descriptions:

Role	Description
db_accessadmin	Add or remove groups, users or SQL Server users to database
db_backupoperator	Back up database
db_datareader	View data in all user tables in database
db_datawriter	Add, modify, or delete data from all user tables
db_ddladmin	Execute data definition language commands in the database (create tables, insert data etc.)
db_denydatareader	Drop privileges for users to view/select data
db_denydatawriter	Drop privileges for users to modify/delete data db_owner	Database owner; broad DBA privileges and full control
db_securityadmin	Manage statement and data object permissions

You may add users to fixed database roles by using the Management Server GUI or by running special System stored procedures on the command line within a new query window. To assign a database role to a user through the GUI, right click on the role and click 'properties' (expand the database by clicking on the '+' sign next to its name; now, expand 'Security', 'Roles', and 'Database Roles' and right click on the role). Click 'Add' to open the 'select login' dialogue box. Here, you may click the browse button to view available logins. Check the box next to a login and click OK to add the login to the selected role.

View Database Role Properties

Alternatively, you may execute a system stored procedure on a query window to add a user to a database role. The sp_addrolemember procedure may be used for this purpose:

Add Account to Role:
sp_addrolemember <role_name>, <member_account>
Drop Account from Role:
sp_droprolemember <role_name>, <member_account>

Every database user is assigned the Public role. This role contains default access permissions for all users of the database. Each user of the database has at least the permissions assigned to the Public role. The public role cannot be dropped. If you require custom roles and groups with permissions that range beyond common tasks, you may create and define new database roles using the Management Server GUI or the sp_addrole stored procedure.

Add new Role:
sp_addrole <role_name> , <role_owner>
Remove Role:
sp_droprole <role_name>

An application role defines the permissions for specific database applications. For example, consider a GUI based software that allows users to create and save graphics schemes using graphics elements stored in a specific SQL server database. This software hides the actual database retrieval and insertion of the schemes from the user but performs these operations internally. In such cases, it makes more sense to assign roles to the application rather than to all users who access the application. An application role may be defined to accomplish this type of security. A new application role may be added using the SQL server GUI. Expand the database, 'Security', and 'Roles'. Right click on 'Application Role' -> 'New Application Role...' and enter information in the pop up dialogue. Alternatively, you may use the sp_addapprole stored procedure.

Add Application Role:
sp_addapprole <role_name>, <account_password>
Delete Application Role:
sp_dropapprole <role_name>