SQL Server role member identities and permissions

To facilitate the management of database permissions,SQL ServerSeveral "Roles" are provided, which are security subjects used to group other subjects. They are similar to groups in Microsoft Windows operating systems. The permission scope of a database-level role is the database scope. SQL Server has two types of database-level roles: pre-defined"Fixed database role"And the" flexible database role "that you can create ".

A fixed database role is defined at the database level and exists in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role identities. However, only members of the db_owner database role can add members to the db_owner fixed database role. MSDB databases also have some special-purpose fixed database roles. You can add any database account and other SQL Server roles to a database-level role. Each member of a fixed database role can add other login names to the same role.

Database-level role name and description

Members of the db_owner fixed database role can perform all database configuration and maintenance activities and delete the database.

Members of the db_securityadmin fixed database role can modify the role Member identity and Management permissions. Adding a principal to this role may lead to unexpected permission upgrades.

Members of the db_accessadmin fixed database role can add or delete database access permissions for the Windows logon name, Windows Group, and SQL Server logon name.

Members of the db_backupoperator fixed database role can back up the database.

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in the database.

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

Members of the db_datareader fixed database role can read all data from all user tables.

Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the User table in the database.

Members of the db_denydatareader fixed database role cannot read any data from the user table in the database.

For specific information on database-level fixed role permissions, see fixed database role permissions (database engine ).

MSDB role name and description

Db_ssisadmin

Db_ssisoperator

Db_ssisltduser

Members of these database roles can manage and use SSIs. An SQL server instance upgraded from an earlier version may containAn old role named after ta transformation services (DTS) instead of SSIs. For more information, see use the integration services role.

Dc_admin

Dc_operator

Dc_proxy

Members of these database roles can manage and use data collectors. For more information, see Data Collector security.

Policyadministratorrole

Members of the DB _ policyadministratorrole database role can perform all configuration and maintenance activities on policy-based management policies and conditions. For more information, see manage servers using policy-based management.

Servergroupadministratorrole

Servergroupreaderrole

Members of these database roles can manage and use registered server groups. For more information, see create a server group.

Important:

Members of the db_ssisadmin and dc_admin roles may be able to escalate their permissions to SysAdmin. This permission improvement occurs because these roles can modify the integration services package, while SQL server can use the SysAdmin security context of the SQL Server proxy to execute the integration services package. To prevent this permission escalation when running maintenance plans, data collection groups, and other integration services packages, configure the SQL Server proxy job of the running package to use a proxy account with limited permissions, or add only SysAdmin MEMBERS TO THE db_ssisadmin and dc_admin roles.

Use server-level roles

Sp_helpdbfixedrole (TRANSACT-SQL) → metadata → return the list of fixed database roles.

Sp_dbfixedrolepermission (TRANSACT-SQL) → metadata → display the permissions of a fixed database role.

Sp_helprole (TRANSACT-SQL) → metadata → return information about roles in the current database.

Sp_helprolemember (TRANSACT-SQL) → metadata → return information about members of a role in the current database.

SYS. database_role_members (TRANSACT-SQL) → metadata → returns a row for each member of each database role.

Is_member (TRANSACT-SQL) → metadata → indicates whether the current user is a member of the specified Microsoft Windows Group or Microsoft SQL Server database role.

Create role (TRANSACT-SQL) → command → create a new database role in the current database.

Alter role (TRANSACT-SQL) → command → change the name of the database role.

Drop role (TRANSACT-SQL) → command to delete a role from the database.

Sp_addrole (TRANSACT-SQL) → command → create a new database role in the current database.

Sp_droprole (TRANSACT-SQL) → command → delete a database role from the current database.

Sp_addrolemember (TRANSACT-SQL) → command → Add a database user, database role, Windows login name, or Windows Group to the database role in the current database.

Sp_droprolemember (TRANSACT-SQL) → command → Delete the security account from the SQL Server role of the current database.

Public database role

Each database user belongs to the public database role. If a user is not granted or denied specific permissions on a security object, the user inherits the permissions granted to the public role of the object.

This article will introduce you here. Due to my limited level, if you have any improper descriptions in this article, you are welcome to criticize and correct me. Thank you.

 

 

This article from: http://database.51cto.com/art/201107/276065.htm