SQL Server Roles: A Practical Guide

What are SQL Server Roles?

SQL Server roles lets you group user logins together and manage server-level permissions. They play a central part in SQL Server security. SQL Server has two types of roles:

Fixed server roles, which are built into SQL Server, and do not allow you to modify permissions or user-defined roles. We cover the main fixed server roles below.
User-defined roles, which you can customize for your organization’s security requirements.

When managing a SQL Server database, it is important to maintain the least privileges rule, which ensures that users gain access only to the data they need. The goal is to enable users to access data needed for normal operations but nothing beyond that. You can restrict and control user access by setting up server roles.

In this article, you will learn:

SQL Server Role Types
- Server-Level Roles
- Database-Level Roles
- Application Roles
Creating a Server Role Using SQL Server Management Studio
SQL Server Roles with Satori

SQL Server Role Types

SQL Server provides three types of roles you can use to restrict access to data in your database: server-level roles, database-level roles, and application-level roles.

Server-Level Roles

Server-level roles help manage permissions for the entire SQL Server instance. SQL Server provides several built in server roles, but you should add your own specific roles if possible. Fixed server roles cannot be changed.

You can also assign server-level principals (Windows groups and accounts, and SQL server logins) to these roles. Fixed server roles allow members to add other users to the same role, but this is not so for user-defined server roles.

SQL Server provides the following fixed server roles, starting with least privileged roles:

public—default role for server principals who do not have specific securable object permissions. Only assign public permissions to objects that can be made available to all users. You cannot revoke public permission from any server role.
dbcreator—can alter, create, drop, or restore databases.
diskadmin—can manage disk files.
bulkadmin—can execute BULK INSERT
setupadmin—can add/remove linked servers and run Transact-SQL
processadmin—can end running processes in the SQL server instance.
securityadmin—can administer logins, can reset SQL server login passwords, and grant, deny or revoke server-level permissions or database-level permissions
serveradmin—can alter server configuration and shut it down
sysadmin—can perform all server activities.

Database-Level Roles

SQL server defines roles that enable management of database-wide permissions. You can use the ALTER ROLE statement to add and remove users to database roles.

Like server-level roles, there are fixed database-level roles built into SQL Server, and you can create additional roles, customizing them using the GRANT, DENY, and REVOKE statements.

Fixed roles exist independently for each database within your SQL Server instance. The db-owner server role is allowed to manage membership of fixed database roles.

Microsoft SQL Server provides the following fixed database roles:

db_owner—allowed to perform all maintenance and configuration activities on the database, as well as dropping the database
db_securityadmin—can modify custom role memberships and manage permissions. Monitor this role closely as it has the ability to escalate privileges.
db_accessadmin—can add/remove database access for Windows groups and logins, as well as SQL Server logins
db_backupoperator—can perform database backups
db_ddladmin—can run data definition language (DDL) commands
db_datawriter—can add, change, or delete any user table data.
db_datareader—limited to reading data from user tables
db_denydatawriter—are not allowed to add, modify or delete user table data
db_denydatareader—cannot read any of the data in a user table

In addition to database-level roles, SQL Server also enables defining permissions at the row level.

Read our guide to SQL Server row level security (coming soon)

Application Roles

Application roles facilitate applications to run with dedicated user-like permissions. This allows all users, connected through a pre-specified application, to access specific data in a database. To distinguish them from regular database roles, they are inactive by default and have no members.

You can activate application roles by using the password-protected sp_setapprole command. Since these roles constitute a database-level principal, they require guest– level permissions to access other databases. If guest is disabled in a database, the database will be inaccessible to application roles from other databases.

Since they are not associated with a server-level principal, application roles are denied access by default to server-level metadata in SQL server (it is possible to remove this restriction if necessary).

This is the process an application should follow to grant access to the database for an application user:

User logs into the application, and the application connects to SQL Server, identifying itself as the user
The application executes the sp_setapprole stored procedure using the application-specific password
If role name and password are valid, the application role is enabled
From this point onwards, the database grants application role permissions to the user. Permissions remain valid until the connection is closed.

Learn more:

Creating a Server Role Using Management Studio

The below walkthrough shows how to create a server role in SQL Server using Management Studio.

Step 1:
In the Object Explorer, choose a SQL Server instance, find the Security folder and expand it. Right-click Server Roles > New Server Role.

Step 2:
In the New Server Role screen, go to the General page. Next, find the -server_role_name dialog box and type a name for the role.

Step 3:
Under Owner, select a server principal to attach to the new role.

Step 4:
Under Securables, you will find a list of server-level securables. Select one or more; you can grant or deny permissions to each securable for your server role.

Step 5:
In the Permissions: Explicit box, choose the check box to “grant, grant with grant, or deny permission” to this server role for the selected securables. In some cases it will not be possible to set permission for all selected securables, and you will see a partial selection.

Step 6:
In the Members page, click Add to add logins (groups or individuals) to your new server role.

Step 7:
In the Memberships page, you can also select the appropriate checkbox to add a user-defined server role as a member of another server role.

Click OK, and you have successfully defined a new server role for your SQL Server instance.

SQL Server Access Control with Satori

Satori allows SQL Server admins to automate and streamline users access to data. With Satori’s Users Directory you can organize users into groups based on project or scope and with Satori’s Datasets you can organize SQL Server entities such as tables into logical datasets. Then you can provide groups and users entitlement to specific datasets as well as manage manual or automated data access approval workflows.

Learn more: