SQL Server Fixed Server Roles

Have we ever wondered why does SQL Server provide many fixed server roles? What all“Server roles” are available for grabs? What of this each help us in? And why do we need these many?

Well the simple answer is ‘there are ‘N’ number of users accessing and viewing data from the database’ which makes ‘SECURITY’ a major concern area for any Database administrator.

There are different levels of ROLES defined in SQL Server - Server roles and Database roles.

For now let us consider ‘Server roles’ and understand more on the same. From SQL Server Management Studio – Under ‘Security’, and then under ‘Server Roles’ we have the following roles listed. Each of this role helps manage the permissions on a server. There are in total ‘NINE’ fixed server roles. These are system level server roles and cannot be changed at any point in time. Until SQL Server 2008 we did not have the flexibility of creating user defined server roles, SQL Server 2012 does provide the functionality of user defined server roles.

Each of the fixed server role and description is as explained below: (From MS Books online)

Fixed server-level role	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. 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. You cannot change membership in public. Note public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public.

For more reading – we can refer to Brian Kelley article on MSSQL Tips
ReferenceLink