Detailed description of server role and database role permissions in SQL Servers

When several users need to perform similar actions in a particular database, where there is no corresponding Windows user group, you can add a role to the database. A database role specifies a set of database users who can access the same database objects.

The members of a database role can be divided into the following categories:

Windows user groups or user accounts

SQL Server Login

Other roles

The security architecture of SQL Server includes several roles that contain specific implied permissions. In addition to the roles created by the database owner, there are two types of predefined roles. The roles that can be created can be grouped into the following categories:

fixed server

Fixed database

User definable

fixed server

Because fixed servers are defined at the server level, they are located outside the database that belongs to the database server. The following table lists all the existing fixed server roles.

fixed server role	Description
Sysadmin	Perform any action in SQL Server
ServerAdmin	Configure server Settings
Setupadmin	Installing the replication and management extension process
Securityadmin	Manage logins and CREATE DATABASE permissions and read audits
Processadmin	Managing SQL Server Processes
DBCreator	Creating and modifying databases
Diskadmin	Managing Disk Files

The following two system procedures are used to add or remove fixed server role members:

Sp_addsrvrolemember

Sp_dropsrvrolemember

Note: You cannot add, modify, or remove fixed server roles. In addition, only members of the fixed server role can perform these two system procedures to add or remove login accounts from the role.

SA login

The sa login is the system administrator's login. The role does not exist in the previous version of SQL Server, and the sa login has all possible permissions on system administration work. In SQL Server 2005, the sa login maintains backward compatibility. The sa login is always a member of the fixed server role Syadmin and cannot be removed from the role.

Note: Use the sa login only if there is no other way to log on to the SQL Server system.

fixed server roles and their permissions

In a SQL Server system, each fixed server role has its implied permissions. Use the system procedure sp_srvrolepermission to browse permissions for each fixed server role. The syntax of the system process is:

sp_srvrolepermission[[@srvrolename =] ' role ']

If you do not specify a value for role, all permissions for the fixed server role are displayed. The following sections discuss the permissions for each fixed server role.

1. The sysadmin

Fixed server role members of the sysadmin are given all possible permissions in the SQL Server system. For example, only members of this role (or a user who is assigned the CREATE DATABASE permission by a member in this role) can create a database.

There is a special relationship between fixed server roles and SA logins. The sa login is always a member of the fixed server role and cannot be removed from the role.

2. serveradmin

Members of the fixed server role serveradmin can perform the following actions:

Add additional logins to the server role

Run the DBCC PINTABLE command (so that the table resides in main memory)

Run the system procedure sp_configure (to display or change system options)

Run the RECONFIGURE option (to update all changes made by the system procedure sp_configure)

Use the shutdown command to shut down the database server

Run the system procedure sp_tableoption values for the user-defined table setting options

3. setupadmin

Members in the fixed server role setupadmin can perform the following actions:

Add additional logins to the server role

Add, remove, or configure a linked server

Perform some system procedures, such as sp_serveroption

4. securityadmin

Members in the fixed server role securitypadmin can perform all actions on server access and security. These members can perform the following system actions:

Add additional logins to the server role

Read the SQL Server error log

Run the following system procedures: such as sp_addlinkedsrvlogin, sp_addlogin, sp_defaultdb, Sp_defaultlanguage, Sp_denylogin, sp_droplinkedsrvlogin , Sp_droplogin, sp_grantlogin, Sp_helplogins, Sp_remoteoption, and sp_revokelogin (all of these system processes are related to system security.) )

5. processadmin

The members in the fixed server role processadmin are used to manage SQL Server processes, such as aborting queries that the user is running. These members can perform the following actions:

Add additional logins to the server role

Execute KILL command (to cancel user process)

6. dbcreator

The members in the fixed server role dbcreator are used to manage all actions related to database creation and modification. These members can perform the following actions:

Add additional logins to the server role

Run the CREATE DATABASE and ALTER DATABASE statements

Use system procedure sp_renamedb to modify the name of the database

7. diskadmin

Members of the fixed server role diskadmin can do the following actions related to the files and filegroups used to store database objects:

Add additional logins to the server role

Run the following system procedures: Sp_ddumpdevice and sp_dropdevice.

Run the DISK INIT statement

Fixed database role

Fixed database roles are defined on the database tier, so they exist in each database that belongs to the database server. The following table lists all the fixed database roles.

Fixed database role	Description
db_owner	Users who can perform all the technical actions in the database
Db_accessadmin	You can add, remove users from a user
Db_datareader	Users who can view data from user tables in all databases
Db_datawriter	Users who can add, modify, or delete data from user tables in all databases
db_ddladmin	Users who can perform all DDL operations in the database
Db_securityadmin	Users who can manage all actions related to security permissions in the database
Db_backoperator	Users who can back up the database (and can publish DBCC and CHECKPOINT statements, which are typically executed before the backup)
Db_denydatareader	Users who cannot see any data in the database
Db_denydatawriter	Users who cannot change any data in the database

In addition to the fixed database roles listed in the previous table, there is a special fixed database role called public, which is described first.

Public role

The public role is a special fixed database role that is owned by every legitimate user of the database. It provides all the default permissions for users in the database. This provides a mechanism for giving a certain (usually limited) permission to all users who do not have the appropriate permissions. The public role retains the default permissions for all users in the database and therefore cannot be deleted.

In general, the public role allows users to do the following:

Use some system procedures to view and display information from the master database

Execute some statements that do not require some permission (for example, print)

Fixed database roles and their permissions

In the database, each fixed database role has its own specific permissions. This means that for a database, the permissions of members of a fixed database role are limited. Use the system procedure sp_dbfixedrolepermission to view the permissions for each fixed database role. The syntax for the system procedure is:

sp_db.xedrolepermission [[@rolename =] ' role ']

If you do not specify a value for role, all permissions for the fixed database role can be displayed. The following sections discuss the permissions for each fixed database role.

1. DB_Owner

Fixed database role members of the db_owner can perform the following actions in a particular database:

Add members to or remove members from other fixed database roles

Run all of the DDL statements

Run backup DATABASE and BACKUP LOG statements

To explicitly start the checkpoint process using the CHECKPOINT statement

Run the following DBCC commands: DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB, DBCC UPDATEUSAGE

Grant, cancel, or deprive the following permissions on each database object: SELECT, INSERT, UPDATE, delete, and references

Use the following system procedures to add users or roles to the database: Sp_addapprole, Sp_addrole, sp_addrolemember, Sp_approlepassword, Sp_changeobjectowner, sp_ Dropapprole, Sp_droprole, Sp_droprolemember, Sp_dropuser, sp_grantdbaccess

Use system procedure sp_rename to rename any database object

2. db_accessadmin

Members of the fixed database role db_accessadmin can perform all actions related to database access. These roles can perform the following actions in a specific database:

Run the following system procedures: Sp_addalias, Sp_dropalias, Sp_dropuser, sp_grantdbacess, sp_revokedbaccess

Add or remove access for Windows user accounts, Windows groups, and SQL Server logins

3. DbDataReader

Members of the fixed database role DbDataReader have SELECT permissions on database objects (tables or views) in the database. However, these members cannot grant this permission to any other user or role. (This restriction is also true for REVOKE statements.) )

4. Dbdatawriter

Members of the fixed database role dbdatawriter have INSERT, UPDATE, and delete permissions on database objects (tables or views) in the database. However, these members cannot grant this permission to any other user or role. (This restriction is also true for REVOKE statements.) )

5. db_ddladmin

Members of the fixed database role db_ddladmin can perform the following actions:

Run All DDL statements

Grant Referencese permissions on any table

Use system procedures sp_procoption and sp_recompile to modify the structure of any stored procedure

Use system procedure sp_rename to rename any database object

Use system procedures sp_tableoption and sp_changeobjectowner to modify table options and the owner of any database objects individually

6. db_securityadmin

Members of the fixed database role db_securityadmin can manage security in the database. These members can perform the following actions:

Run all security-related Transact-SQL statements (GRANT, deny, and revoke)

Run the following system procedures: Sp_addapprole, Sp_addrole, sp_addrolemember, Sp_approlepassword, Sp_changeobjectowner, Sp_dropapprole, sp_ Droprole, Sp_droprolemember

7. Db_backupoperator

Members of the fixed database role db_backupoperator can manage the process of database backups. These members can perform the following actions:

Run backup DATABASE and BACKUP LOG statements

To explicitly start the checkpoint process with the CHECKPOINT statement

Run the following DBCC commands: DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB, DBCC UPDATEUSAGE

8. Db_denydatareader and Db_denydatawriter

As the name implies, the members of the fixed database role db_denydatareader do not have SELECT permissions on database objects (tables or views) in the database. This role can be used if the database contains sensitive data and other users cannot read the data.

The members of the fixed database role db_denydatawriter do not have insert, UPDATE, and delete permissions on any database objects (tables or views) in the database.

Detailed description of server role and database role permissions in SQL Servers