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
