web analytics
Sign In | Register
Welcome, Guest
1 2

Understanding Server and Database Roles in SQL Server

 Options
Previous Topic Next Topic

codeling 1595 - 6639
@2020-01-19 19:23:15

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.
@2020-01-19 19:35:23

Fixed Server Roles

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.

Fixed server-level role Description
sysadmin Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)
bulkadmin Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin The diskadmin fixed server role is used for managing disk files.
dbcreator Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.

Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

 

Each fixed server role has certain permissions assigned to it. The following graphic shows the permissions assigned to the server roles.
fixed_server_role_permissions
@2020-01-19 19:39:57

Fixed Database 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.

Fixed-Database 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 in SQL Server. (In SQL Database and SQL Data Warehouse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadmin Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
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.

The permissions assigned to the fixed-database roles cannot be changed. The following figure shows the permissions assigned to the fixed-database roles:

fixed_database_role_permissions
@2020-01-19 19:40:27

dbo vs db_owner

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.
Previous Topic Next Topic
1 2

Comments
You must Sign In to comment on this topic.


© 2024 Digcode.com