MS SQL Server 2012 - DBA Articles

SQL Server - Database Level Fixed Roles

To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.
There are two types of database-level roles in SQL Server: fixed database roles that are predefined in the database and flexible database roles that you can create.
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also some special-purpose fixed database roles in the msdb database.
The following table shows the fixed database-level roles and their capabilities. These roles exist in all databases.

Database-level Fixed Role Name	Description
db_owner	Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin	Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin	Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator	Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin	Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter	Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter	Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader	Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.
More Reference URL: http://msdn.microsoft.com/en-us/library/ms189121%28v=sql.110%29.aspx

* * * * *

Your Comment To AUTHOR