Preamble
SQL Server applies role-based security rights delimitation. A role is a certain set of rights that can be assigned to a certain user or group of users.
In SQL Server there are default server and database level roles, which have a predefined set of permissions assigned to them.
You can also create your own roles for which the administrator can define a list of permissions and bans. Default Server and Database Level Roles have a predefined set of permissions, each at its own level and they cannot be changed.
The article below describes the default Server and Database Level Roles that can be assigned to users and groups and how to assign them.
Built-in SQL Server server level roles
The table below shows the default server level roles and a brief description of them:
| Role Title | Role description |
| sysadmin | This server role allows you to perform any actions on the server. |
| serveradmin | The serveradmin server roles allow you to change configuration settings at the server level, as well as to turn it off. |
| securityadmin | Those who are assigned this role can manage logins, their rights and their properties. They can also grant, deny and revoke database level permissions if they have access to the database. In addition, they can reset passwords for SQL Server login names.
The securityadmin role can be considered equivalent to the sysadmin role, allowing any permissions to be granted. |
| processadmin | This server role allows you to terminate processes running on an SQL Server instance. |
| setupadmin | The setupadmin server role can add or remove related servers using Transact-SQL instructions . |
| bulkadmin | Owners of this role can follow the BULK INSERT instructions. |
| diskadmin | The diskadmin server role is used to manage files on disk. |
| dbcreator | This role allows you to create, modify, delete and restore any database. |
| public | The public role is contained in each database, including system databases. It cannot be deleted, nor can users be added or removed from it.
The permissions granted to the public roles are inherited by all other users and roles because they belong to the default public role. You should only grant public roles permissions that are required by all users. |
The roles listed in the table are based on the 34 standard server level permissions that are allowed or denied for the built-in role, below is the list of server level permissions:
BULK OPERATIONS
ANY AVAILABILITY GROUP
ANY CONNECTION
ANY CREDENTIAL
ANY DATABASE
ANY ENDPOINT
ANY EVENT NOTIFICATION
ANY EVENT SESSION
ANY CONNECTED SERVER
ANY LOGIN
ANY SERVER AUDIT
ANY SERVER ROLE
ADDITIONAL RESOURCES
SERVER STATE
ALTER SETTINGS
ALTER TRACE
AUTHENTICATE SERVER
CONNECT ANY DATABASE
CONNECT SQL
CONTROL SERVER
CREATE ANY DATABASE
CREATE AVAILABILITY GROUP
CREATE DDL EVENT NOTIFICATION
CREATE ENDPOINT
CREATE SERVER ROLE
CREATE TRACE EVENT NOTIFICATION
EXTERNAL ACCESS ASSEMBLY
IMPERSONATE ANY LOGIN
SELECT ALL USER SECURABLES
SHUTDOWN
UNSAFE ASSEMBLY
VIEW ANY DATABASE
VIEW ANY DEFINITION
VIEW SERVER STATE
Built-in SQL Server Database Level Roles
The table below describes the database-level roles that exist in all databases and summarizes the rights that these roles provide.
All the database-level roles listed below are built-in, and their rights cannot be changed.
|
Role Title
|
Role description
|
|
db_owner
|
This role grants the authority to perform all database configuration and maintenance operations, as well as to delete the database.
|
|
db_securityadmin
|
Roles in db_securityadmin allow you to manage user roles and permissions, including their own.
|
|
db_accessadmin
|
This role allows adding or deleting remote access rights to the database.
|
|
db_backupoperator
|
The role db_backupoperator allows you to create database backups.
|
|
db_ddladmin
|
This role allows you to execute any Data Definition Language (DDL) command in the database.
|
|
db_datawriter
|
This role allows you to add, delete, or modify data in all tables.
|
|
db_datareader
|
The role db_datareader allows you to read all data from all tables.
|
|
db_denydatawriter
|
Those to whom this role is assigned cannot add, change, or delete data in database tables.
|
|
db_denydatareader
|
Owners of this role cannot read data from database tables.
|
The role of the public was described in the table above; it is on every level, including the database level. All default-created roles, whether database-level or server-level, have invariable permissions; you cannot add new permissions or remove them. The only exception is the public role.
At the database level, there is also a list of 77 default permissions, some of which are already included in the server-level roles and permissions:
ALTER
ANY APPLICATION ROLE
ANY ASSEMBLY
ANY ASYMMETRIC KEY
ANY CERTIFICATE
ANY COLUMN ENCRYPTION KEY
ANY COLUMN MASTER KEY
ANY CONTRACT
ANY DATABASE AUDIT
ANY DATABASE DDL TRIGGER
ANY DATABASE EVENT NOTIFICATION
ANY DATABASE EVENT SESSION
ANY DATABASE SCOPED CONFIGURATION
ANY DATASPACE
ANY EXTERNAL DATA SOURCE
ANY EXTERNAL FILE FORMAT
ANY FULL TEXT CATALOG
ALTER ANY MASK
ANY MESSAGE TYPE
ANY REMOTE SERVICE BINDING
ALTER ANY ROLE
ANY ROUTE
ANY SCHEMA
ANY SECURITY POLICY
ANY SERVICE
ANY SYMMETRIC KEY
ALTER ANY USER
AUTHENTICATE
BACKUP DATABASE
BACKUP LOG
CHECKPOINT
CONNECT
CONNECT REPLICATION
CONTROL
CREATE AGGREGATE
CREATE ASSEMBLY
CREATE ASYMMETRIC KEY
CREATE CERTIFICATE
CREATE CONTRACT
CREATE DATABASE
CREATE DATABASE DDL EVENT NOTIFICATION
CREATE DEFAULT
CREATE A FULLTEXT CATALOG
CREATE FUNCTION
CREATE MESSAGE TYPE
CREATE PROCEDURE
CREATE QUEUE
CREATE REMOTE SERVICE BINDING
CREATE ROLE
CREATE ROUTE
CREATE RULE
CREATE SCHEMA
CREATE SERVICE
CREATE SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
DELETE
EXECUTE
EXECUTE ANY EXTERNAL SCRIPT
INSERT
KILL DATABASE CONNECTION
REFERENCES
SELECT
SHOWPLAN
SUBSCRIBE QUERY NOTIFICATIONS
TAKE OWNERSHIP
UNMASK
UPDATE
VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
VIEW ANY COLUMN MASTER KEY DEFINITION
VIEW DATABASE STATE
VIEW DEFINITION
You can get a list of all permissions on the SQL server by executing the command:
SELECT * FROM sys.fn_builtin_permissions('');
The result will look like this:
About Enteros
Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Enteros and CloudTech: Database Performance and RevOps in the BFSI Sector
- 20 February 2025
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Cost Attribution and Estimation in Healthcare: Enhancing Database Performance Monitoring with Enteros
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Driving Database Performance and Observability in the Financial Sector
- 19 February 2025
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Cost Estimation in the Tech Sector: How Enteros Leverages Logical Models and Cloud FinOps for Smarter Database Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…