SQL Server Roles
Server roles are Database Engine–level security principals which enable us to simplify the assignment of permissions at the database instance level.
There are 2 types of server roles
- Fixed – we cannot alter the permissions assigned on all but the public role , but we can modify the membership of these roles
- User Defined – a feature new in SQL Server 2012 and enable us to apply permissions at the Database Engine–instance level
Server roles enable you to simplify the assignment of permissions at the database instance level. SQL Server 2012 ships with nine built-in server roles.
ALTER SERVER ROLE serveradmin ADD MEMBER "mydomain\domain_group_z"
These built-in server roles are fixed and, other than the public role, it is not possible to modify the permissions assigned to these roles:
FIXED SERVER ROLES
- bulkadmin - Members of this are able to use the BULK INSERT statement on databases hosted on an instance
- dbcreator -allows the SQL Server login to perform tasks such as that they can create databases, and can alter and restore their own databases.
- diskadmin - This role is used for managing disk files.
- processadmin - Members can end processes that are running in an instance of SQL Server
- securityadmin - 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
- serveradmin - Members can perform instance-wide configuration tasks, including the ability to shut down the instance
- setupadmin - fixed server role allows for the adding and removing of linked servers and also execute som system stored procedures.
- sysadmin - Members can perform all activities possible on the Database Engine instance. By default, all members of the Windows BUILTIN\Administrators group, the local administrator’s group, are members of the sysadmin fixed server role
- 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
Commands for viewing server roles properties:
- sp_helpsrvrole - a list of fixed server roles eg. EXEC sp_helpsrvrole ‘bulkadmin’
- sp_helpsrvrolemember - fixed server role membership
- sp_srvrolepermission - fixed server role permissions
- is_srvrolemember - enables – check whether a SQL Server login is a member of a specific fixed or user-defined server role
- sys.server_role_members - information about role members, displayed as role and member id
DEFINED SERVER LEVEL ROLES
- We can assign customized permissions to user-defined server roles, we can grant or deny access on the basis of SQL Server login
- CREATE SERVER ROLE statement creates a user-defined server role at the instance rather than at the database level
- The ALTER SERVER ROLE enables us to add security principals at the instance level to a user-defined server role rather than to a flexible database role
Creating the user-defined server role
CREATE SERVER ROLE Modify_Databases;
Granting server-level permissions to the role
GRANT ALTER ANY DATABASE TO Modify_Databases;
Adding SQL Server logins to the role
ALTER SERVER ROLE...[ADD/DROP] MEMBER
To check roles we can use sp _srvrolepermission ‘<role_name>’
To alter the server role we would follow the below process
ALTER SERVER ROLE [<role_name>] ADD MEMBER [<domain\username> GO