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

Source: Internet
Author: User
Tags server error log

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.