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:
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 Role | Description |
|---|---|
| sysadmin | Can perform any activity in SQL Server. |
| serveradmin | Can set server-wide configuration options and shut down the server. |
| setupadmin | Can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using SQL Server Management Studio.) |
| securityadmin | Can 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. |
| processadmin | Can end processes that are running in an instance of SQL Server. |
| dbcreator | Can create, alter, drop, and restore any database. |
| diskadmin | Can manage disk files. |
| bulkadmin | Can execute BULK INSERT statements. |
| 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. 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.