What are SQL Roles ?
Predefined collection of objects and permissions?
Predefined collection of objects and permissions?
Roles allow the dba to manage permissions more efficiently
We first create roles and then assign permissions to roles, and then add logins to the roles
SQL Server supports four types of roles
• Fixed database roles – These roles already have pre – defined set of permissions
• User-defined database roles – These roles have their set of permissions defined by the sa
• Fixed server roles – These roles already have pre – defined set of permissions
• User-defined server roles – These roles have their set of permissions defined by the sa
The following shows the fixed database-level roles and their capabilities. These roles exist in all databases.
Database-level role
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 (be careful)
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.
SQL Server Roles
The Server Roles page lists all possible roles that can be assigned to the new login.
bulkadmin
Members of the bulkadmin fixed server role can run the BULK INSERT statement.
dbcreator
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
diskadmin
Members of the diskadmin fixed server role can manage disk files.
processadmin
Members of the processadmin fixed server role can terminate processes running in an instance of the Database Engine.
public
All SQL Server users, groups, and roles belong to the public fixed server role by default.
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. Additionally, they can reset passwords for SQL Server logins.
serveradmin
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
setupadmin
Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.
Sysadmin (‘God’ like powers – can do anything and everything)
Members of the sysadmin fixed server role can perform any activity in the Database Engine.