Details of server role and database role permissions by E-online 26. September 18:55coming from http://blog.csdn.net/e_online/article/details/4597957 role 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 group or user account SQL Server login Other roles SQL Server security architecture 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. These roles can be created in the following categories: fixed server fixed database user custom 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 install replication and manage extension procedures securityadmin manage logins and create Database permissions and read audit processadmin manage SQL Server processes dbcreator Create and modify databases Diskadmin manage disk files The following two system procedures are used to add or remove fixed server role members: SP_ Addsrvrolemembersp_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 (which causes the table to reside in main memory) to run the system procedure sp_configure ( To display or change system options) to run the RECONFIGURE option (to update all changes made by the system procedure sp_configure) use the shutdown command to turn off the database server run system procedure sp_tableoption the value 3 for the user-defined table setting option. Members of the Setupadmin fixed server role setupadmin can perform some system procedures, such as sp_serveroption4, by adding additional logins to the server role to add, remove, or configure a linked server. 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: Adding additional logins to the server role read SQL Server error logs 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 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 do the following actions: Add another login execution kill command to the server role (to cancel the user process)6. Members in the DBCreator fixed server role dbcreator are used to manage all actions related to database creation and modification. These members can do the following actions: Add additional logins to the server role run the CREATE DATABASE and ALTER DATABASES statement using the system procedure sp_renamedb to modify the name of the DB 7. Members of the Diskadmin fixed server role diskadmin can perform 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 can perform all the technical actions in the database user db_accessadmin can add, remove user db_datareader can view data in all databases in user table db_datawriter can add, Users who modify or delete data in the user tables in all databases db_ddladmin users who can perform all DDL operations in the database db_securityadmin can manage users in the database for all actions related to security permissions 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 not see the user of any data in the database db_ Denydatawriter the user 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 use certain system procedures to view and display information in the master database to perform some statements that do not require permission (for example, print) fixed database roles and their permissions in the database, with specific permissions for each fixed database role. This means that for a database, the permissions of members of a fixed database role are limitedOf 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, the permissions for all fixed database roles 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 specific database: Add members to other fixed database roles, or remove members from them run all DDL statements run backup database and backup The log statement uses the CHECKPOINT statement to explicitly start the checkpoint process to 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 uses 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 do the following 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. Dbdatawriter the members of the fixed database role dbdatawriter the database objects in the database (table or viewimage) with INSERT, UPDATE, and delete permissions. 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 to grant Referencese permissions on any table using the system procedure sp_procoption and SP_ Recompile to modify the structure of any stored procedure using the system procedure sp_rename to rename any database object using the system procedure sp_tableoption and sp_changeobjectowner the option to modify the table individually and the owner of any database object 6. Members of the db_securityadmin fixed database role db_securityadmin can manage security in the database. These members can perform actions such as running all security-related Transact-SQL statements (GRANT, deny, and revoke) running 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 the BACKUP database and BACKUP LOG statements explicitly starting the checkpoint process with the CHECKPOINT statement to run the following DBCC command: 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 rights to any database objects (tables or views) in the database
Server role and database role permissions in SQL
