SQL Server use role based security, which allows Database Administrators to assign particular permissions to users and groups. SQL server has a set of fixed roles at both the server and database levels and user-defined roles can also be created at both server and database levels. The fixed roles have a specified set of permissions but user-defined roles have a user-defined set of permissions applied to them.
Server roles
Server roles have permissions that span the entire server instance. The most powerful server role is the sysadmin role. You should be cautious about assigning logins to this role as members of this role have complete access to the entire server.
The following table shows the server-level roles and their capabilities.
(Source : technet.microsoft.com)
| Server-level role name | 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. |
| 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. |
| 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. |
Database roles
The following table shows the fixed database-level roles and their capabilities. Fixed database roles are defined at the database level and exist in each database. These fixed database roles are similar to concept to the fixed server roles but they relate to access to database objects or access to the database itself, rather than access to all databases on the server.
(Source: technet.microsoft.com)
| Database-level 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. |
Leave a Comment