Server and Database Roles

Useful Terminology

• Authentication – Who you are
• Authorisation – What you can do
• Principal – Someone requesting access to a securable. A group of these is a role
• Securable – A resource that can be secured and you can control access to (table, view etc)
• Role – A group for principals

There are 2 types of roles:

1. Server Roles
2. Database Roles

1) Server roles

Here is a list of server roles, a general description of what they’re used for, and a list of the major permissions associated with each:

Role	Description	Server-level permission
sysadmin	Perform any action	CONTROL SERVER
dbcreator	Create and alter databases	ALTER ANY DATABASE
diskadmin	Manage disk files	ALTER RESOURCES
serveradmin (similar to sysadmin)	Configure server-wide settings	ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE,  ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE
securityadmin	Manage and audit server logins	ALTER ANY LOGIN
processadmin	Manage SQL Server processes	ALTER ANY CONNECTION, ALTER SERVER STATE
bulkadmin	Run the BULK INSERT statement	ADMINISTER BULK OPERATIONS
setupadmin	Configure replication and linked servers	ALTER ANY LINKED SERVER
public	Default role assigned to logins	VIEW ANY DATABASE, CONNECT on default endpoints (permissions can be altered)

• Query sys.server_principals for a list of available SQL Server roles
• Query sys.sysusers for a list of SQL Server user groups
• Query sys.server_role_members for current SQL Server roles

Roles and permissions are not the same. If you have a role, you have certain permissions. If you have permissions, you do not necessarily have a role.

To assign a role
First, create a login:

CREATE LOGIN TestLogin WITH PASSWORD = 'Pa$$w0rd',
CHECK_POLICY = OFF;
GO

Then add someone to a server role:

EXEC sp_addsrvrolemember TestLogin, serveradmin;
GO

To drop someone from a server role:

EXEC sp_dropsrvrolemember TestLogin, serveradmin;
GO

The following lets you view the current logins and their server roles:

SELECT r.name AS RoleName,
p.name AS PrincipalName
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS r
ON srm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals AS p
ON srm.member_principal_id = p.principal_id;
GO

2) Database roles

Unlike server roles, it is possible to create user-defined roles with much more specific permissions, as well as assigning a user to different roles on different databases. In fact it is better to create user-defined roles as fixed-roles often offer permissions more extensive than the user needs.
Here is a list of fixed database roles:

Role	Description
db_owner (dbo)	Perform any configuration and maintenance activities on the DB and can DROP it (sa and all members of sysadmin auto assigned)
db_securityadmin	Modify role membership and manage permissions
db_accessadmin	Add/remove access to the DB for logins
db_backupoperator	Backup the DB
db_ddladmin	Run any DDL command in the DB
db_datawriter	Add, delete, or change data in all user  tables
db_datareader	Read all data from all user  tables
db_denydatawriter	Cannot add, delete, or change data in user tables
db_denydatareader	Cannot read any data in user tables

To assign a login to a database role
First, create the login on the database (previously it was on master):

CREATE USER TestLogin
FOR LOGIN TestLogin;

Then assign them a role

EXEC sp_addrolemember db_backupoperator, TestLogin;
GO

To drop a login from a database role:

EXEC sp_droprolemember db_backupoperator, TestLogin;
GO

The following lets you view the current logins and their database roles:

SELECT r.name AS RoleName,
p.name AS PrincipalName
FROM sys.database_role_members AS drm
INNER JOIN sys.database_principals AS r
ON drm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS p
ON drm.member_principal_id = p.principal_id;
GO