SQL Server 2014 - Server Roles

Server roles are assigned to a SQL Server login to determine its level of access.

When we created a SQL Server login, we had the option of assigning the login one or more server roles. Server roles (not to be confused with database roles) are available to manage permissions on a server.

Viewing the Server Roles

To view the server roles in SQL Server 2014 Management Studio, from the Object Explorer, expand the Security folder:

Screenshot of viewing server roles

SQL Server 2014 provides nine fixed server roles. The permissions that are granted to the fixed server roles cannot be changed.

Explanation of the Nine Fixed Server Roles

Here's an explanation of the server roles defined in SQL Server 2014 during setup:

Server RoleDescription
sysadminCan perform any activity in SQL Server.
serveradminCan set server-wide configuration options and shut down the server.
setupadminCan add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using SQL Server Management Studio.)
securityadminCan 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. They can also reset passwords for SQL Server logins.
processadminCan end processes that are running in an instance of SQL Server.
dbcreatorCan create, alter, drop, and restore any database.
diskadminCan manage disk files.
bulkadminCan execute BULK INSERT statements.
publicEvery 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.

Some of these roles allow very specific tasks to be performed. If you only have a small team, it's likely that you'll only use one or two of these roles (including sysadmin). If you have a large team, you might use them all.

In SQL Server 2014 (and SQL Server 2012), you can create user-defined server roles and add server-level permissions to the user-defined server roles.

Next we'll look at database schemas.