SQL Server role and database role permissions detailed

Source: Internet
Author: User
Tags server error log

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

Transferred from: http://blog.csdn.net/e_online/article/details/4597957

Roles when several users need to perform similar actions in a particular database (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-defined fixed servers 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. The sa login 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 member of the sysadmin is 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. Members of the ServerAdmin 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 the value 3 for the user-defined table setting option. Members of the Setupadmin 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_serveroption4. Members of the securityadmin 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. The members in the Processadmin 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 the KILL command (to cancel the user process) 6. The members in the DBCreator 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 the system procedure sp_renamedb to modify the name of the database 7. Members of the Diskadmin 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.
The Run DISK INIT statement fixed database role fixed database role is 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. The public role 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
Perform some statements (such as print) that do not require some permissions 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 the system procedure sp_rename to rename any database object 2. Members of the db_accessadmin 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 3 for Windows user accounts, Windows groups, and SQL Server logins. Members of the DbDataReader 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. Members of the Dbdatawriter 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. Members of the db_ddladmin 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 the system procedure sp_tableoption and sp_changeobjectowner to modify the table options and the owner of any database object 6, respectively. Members of the db_securityadmin 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_droprolemember7. 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 UPDATEUSAGE8. Db_denydatareader and Db_denydatawriter as the name implies, 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.

SQL Server role and database role permissions detailed

Related Article

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.