6.5 Database Roles

6.5 Database Roles

6.5.1 Database Roles

To make it easier to manage permissions in the database, you can set database roles within the database scope.

6.5.2 Fixed database role

SQL Server Pre-defines the following database roles.

Database roles	Description
Db_accessadmin	Members can add or remove database access permissions for Windows logins, Windows groups, and SQL Server logins.
Db_backupoperator	Members can back up the database.
Db_datareader	Members can read all the data from all user tables.
Db_datawriter	Members can add, delete, or change data in all user tables.
db_ddladmin	Members can run any data definition language (DDL) commands in the database.
Db_denydatareader	Members cannot read any data from user tables in the database.
Db_denydatawriter	Members cannot add, modify, or delete any data from user tables in the database.
db_owner	Members can perform all configuration and maintenance activities for the database and can also delete the database.
Db_securityadmin	Members can modify role memberships and administrative permissions. Adding a principal to this role may result in unexpected permission escalation.

Public

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

6.5.3 user-defined database roles

You can also create flexible database roles in SQL Server.

The name of a database role can contain 1 to 128 characters, including letters, symbols, and numbers. The name of a database role cannot contain a backslash ("\"), cannot be null, or an empty string.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/79/0E/wKiom1aGbl6gO-5iAAD4DrpdcqE695.png "title=" new. png "alt=" Wkiom1agbl6go-5iaad4drpdcqe695.png "/>

You can specify an owner for the new role at the same time. If the owner is not specified, the database user performing the current operation (for example: dbo) will have the role.

Examples of T-SQL syntax are:

CREATE ROLE [DbRole1] CREATE ROLE [DbRole2] AUTHORIZATION [dbo]

Requires the CREATE role permission on the database or has membership in the db_securityadmin fixed database role. When you use the AUTHORIZATION option, you also need the following permissions:

To assign ownership of a role to another user, you need to have IMPERSONATE permissions on that user.

To assign ownership of a role to another role, you need to have membership in the assigned role or ALTER permission on the role.

To assign ownership of a role to an application role, you need to have ALTER permission on the application role.

Best Practices:

Do not add a user-defined database role as a member of a fixed role. This may result in unexpected permission escalation.

6.5.4 adding members to a database role

You can add a user or data to a role in the members list of a role.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/79/0E/wKiom1aGcK3APgh_AABi6Sp8fE8172.png "title=" Add member. png "alt=" Wkiom1agck3apgh_aabi6sp8fe8172.png "/>

You cannot add a role as a member of the role itself, or you will report the "cannot make a role a member of its own" error.

Examples of T-SQL syntax are:

ALTER ROLE [DbRole1] ADD MEMBER [sqluser002]

Tips:

It is not recommended to use stored procedures such as sp_addrole, sp_addrolemember .

This article from "SQLServer2014 series" blog, declined reprint!

6.5 Database Roles