System and Application Softwares
Understanding Server and Database Roles in SQL Server
All versions of SQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. Fixed server and fixed database roles have a fixed set of permissions assigned to them.
SQL Server provides server-level roles to help you manage the permissions on a server. Fixed server roles have a fixed set of permissions and server-wide scope. They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed.
SQL Server provides nine fixed server roles. The following table shows the fixed server-level roles and their capabilities.
Each fixed server role has certain permissions assigned to it. The following graphic shows the permissions assigned to the server roles.
To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. Members of the db_owner role can perform all configuration and maintenance activities on the database.
The following table shows the fixed-database roles and their capabilities. These roles exist in all databases. Except for the public database role, the permissions assigned to the fixed-database roles cannot be changed.
The permissions assigned to the fixed-database roles cannot be changed. The following figure shows the permissions assigned to the fixed-database roles:
n every database there is a special user, dbo. While dbo has the effective permissions of a database owner, it is not one and the same as the db_owner role. Any user account can be assigned to the db_owner role, giving that user complete control of the database. The dbo user account is a special account which all members of the sysadmin role are implicitly mapped to. Also, the system table sysdatabases (located in master) stores who is the owner of the database (and is assigned the dbo user account) in the sid field. So not only are sysadmins mapped to dbo, but so is the database owner. The dbo user will always have db_owner rights to a given database, but it is more than just a default user account.
The reason the sid of the database owner is important is in the case of a damaged database. Normally, a db_owner role member could restore a database, but the information on who belongs to the db_owner role is stored within the database itself. If the database is damaged, SQL Server won’t be able to determine who are members of the db_owner role. The only logins it knows for certain have the appropriate rights is the db_owner (by virtue of the information stored in sysdatabases) and those who are assigned to the sysadmin fixed server role (since they have all rights anyway). Any time SQL Server cannot obtain the information about the db_owner database role because the database is unavailable, the actual owner (apart from the sysadmins) will be the only one who can perform such functions.
Another difference deals with permissions. If a user is a member of the db_owner role but not the dbo, DENY permissions still apply. In other words, if I issue a DENY to prevent a particular user from executing a stored procedure (or selecting data from a table, etc.), the user will be unable to issue the execute statement (or SELECT, etc.) as SQL Server will prohibit the user from doing so. The dbo, however, will be able to carry out the command since the dbo naturally bypasses all permissions checks within the database.
© 2020 Digcode.com. All rights reserved.