Server-Level Roles
To easily manage the permissions on your server, SQL Server provides several roles, which are security principals that group other principals. Roles are like groups in the Microsoft Windows operating system.
Fixed server roles are provided for convenience and backward compatibility. Assign more specific permissions whenever possible.
Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope.
You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role.
The following table shows the server-level roles and their capabilities.
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. Additionally, they can reset passwords for SQL Server logins. .gif "Security note") Security Note
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. |
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. |
For specific information about server-level role permissions, see Permissions of Fixed Server Roles (Database Engine).
Working with Server-Level Roles
The following table explains the commands, views and functions for working with server-level roles.
Feature |
Type |
Description |
|---|---|---|
Metadata |
Returns a list of server-level roles. |
|
Metadata |
Returns information about the members of a server-level role. |
|
Metadata |
Displays the permissions of a server-level role. |
|
Metadata |
Indicates whether a SQL Server login is a member of the specified server-level role. |
|
Metadata |
Returns one row for each member of each server-level role. |
|
Command |
Adds a login as a member of a server-level role. |
|
Command |
Removes a SQL Server login or a Windows user or group from a server-level role. |
See Also