Tuesday, July 3, 2012

SQL Server roles

There are two types of roles when it comes to databases - server roles and database roles. Server roles should only be assigned for the advanced users, such as database administrators. Here are some server roles:

Role Description
sysadmin Can perform any task in SQL Server
serveradmin Can set server-wide configuration options, can shut down the server
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and database permissions, read logs, change passwords
processadmin Can manage processes running in SQL Server
dbcreator Can create, alter, and drop databases
diskadmin Can manage disk files
bulkadmin Can execute BULK INSERT statements
public Every SQL Server user account belongs to this 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 an object when you want the object to be available to all users

Database roles are security roles that exist on a database level, as opposed to the server level. There are two kinds: fixed roles and flexible roles. Fixed roles automatically exist in each database. Adding a user to a fixed role will not change user's permissions in any other database. Flexible database-level roles are simply defined as roles that you create in the database.
Here are the fixed database roles:
Role Description
db_owner can make extensive modifications to all aspects of the database. A user who is a db_owner can even drop the database. Here are some actions that can be performed by the user in db_owner role:
  • Creating users
  • Adding them to roles
  • Creating tables/views and stored procedures
  • Adding security settings for tables, views, and stored procedures
db_securityadmin Can modify role permissions and manage permissions with the exception of adding users to db_owner or other fixed database roles. that requires membership is db_owner role
db_accessadmin Users in this role have the ability to change database access. They can grant and revoke access to Windows logins, Windows groups and SQL Server logins. The users that they grant access to will be members of the Public role and will have all the privileges associated with that role
db_backupoperator Can create database backups but do not have permissions to restore the databases they backup. Only users in db_owner database role or in sysadmin server role or dbcreator server role can restored databases
db_ddladmin Can execute DDL commands (CREATE, ALTER, DROP) within the current database. Those users are able to create, or change, new tables, views, indexes and stored procedures. However, they cannot create users or manage security
db_datawriterCan INSERT, UPDATE, and DELETE data from any user created table
db_datareader Can read all data from all the user tables
public Every database user is a member of the Public role. If a user does not have any explicit permissions on a database object, they will inherit the permissions of the Public role. It is important to note that users cannot be removed from the Public role.

No comments:

Post a Comment