Permissions: Create and Maintain Fixed Roles

Custom Roles:

Roles can be created by any user assigned to the CREATE ROLE permission or a member of the db_securityadmin fixed database role.

Database Roles:

SQL Server does indeed come with several built in fixed database roles. They are:

• db_owner
• db_securityadmin
• db_accessadmin
• db_backupoperator
• db_ddladmin
• db_datawriter
• db_datareader
• db_denydatawriter
• db_denydatareader

Description:

db_owner   

• can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server.

db_securityadmin   

•  can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.

db_accessadmin   

• can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator   

•  can back up the database.

db_ddladmin   

•  can run any Data Definition Language (DDL) command in a database.

db_datawriter   

•  can add, delete, or change data in all user tables.

db_datareader   

•  can read all data from all user tables.

db_denydatawriter   

• cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader   

•  cannot read any data in the user tables within a database.

Mapping Database Roles to Users using SSMS:
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.

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.

Mapping Database Roles to Users using TSQL Script:

Add User to Role using Script:

Additionally you can add users to roles by scripting it out.  The script below demonstrates adding a user (Anurag) to the db_datareader and db_datawriter roles on the AdventureWorks2012 database.

Transact-SQL:

USE [AdventureWorks2012] ALTER ROLE [db_datareader] ADD MEMBER [Anurag] GO USE [AdventureWorks2012] ALTER ROLE [db_datawriter] ADD MEMBER [Anurag] GO

Server Roles:

SQL Server does indeed come with several built in fixed server roles. They are:

• sysadmin
• bulkadmin
• dbcreator
• diskadmin
• processadmin
• securityadmin
• serveradmin
• setupadmin

Description:

sysadmin

• This role can do anything within SQL Server.
• This role completely bypasses all security checks.
• This role can do everything any other role can do and more.
• This is the most powerful role in SQL Server. Limit its access to only those who absolutely need it.

bulkadmin

• This role allows the import of data from external files.
• Even being a member of this role isn't enough. You also have to have INSERT rights on the table.
• can run the BULK INSERT statement

dbcreator

• This role allows creation of databases within SQL Server.
• can create, alter, drop, and restore any database.

diskadmin

• used for managing disk files

processadmin

• This is a powerful role because it can kill connections to SQL Server.
• can end processes that are running in an instance of SQL Server

securityadmin

• Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins

serveradmin

• This role manages the SQL Server configuration.
•  can change server-wide configuration options and shut down the server

setupadmin

• Setup admin basically gives control over linked servers.
• can add and remove linked servers

Assign Server Roles to Logins Using SSMS:

Using SQL Server Management Studio, expand the Security option (at the server level, not at the database level) and right click logins.

Right Click on Login Name & Choose Properties:

Goto Server Roles:

Another method for adding logins to a particular server 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 connect to object explorer, expand the Security drop-down and then expand Server Roles. Here you will see the familiar list of fixed server roles with assigned logins.

Assign Server Roles to Logins Using TSQL:

Below code snippet will add new SQL member to existing Server Role: sysadmin

USE [master]

GO
ALTER SERVER ROLE [sysadmin]
ADD MEMBER [Anurag];
GO