SQL Server role member identity permission)

From: http://www.lao8.org/html/8/2009-7-19/2009719130148.html

 

Each role has different permissions. SQL Server has many permissions. You can combine these permissions or assign them to different roles separately, so that users of different roles have different permissions, all the roles you listed are built-in roles. Let's take a look at the following information to help you solve your problem.

To facilitate the management of database permissions, SQL Server provides several "Roles" that are used to group security subjects of 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: the pre-defined "fixed database role" in the database 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.

Important:

Do not add a flexible database role as a fixed-role member. This will lead to unexpected permission upgrades.

The following table shows the fixed database-level roles and their actions that can be performed. All databases have these roles.

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 ).

The MSDB role MSDB database contains the special-purpose roles shown in the following table.

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 contain an old role named after data 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

The following table describes the commands, views, and functions used for server-level roles.

Function type description

• 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.