SQL Server principals – Server Roles

[MSSQLFUN & Decode ITeS becomes a family of 1700 individuals across the globe. Please join us on
Facebook & YouTube.]

Server Roles: Server roles are security principals to control access of other principals. Server roles are server scoped and provide server-level permissions to logins.

• Fixed / Built-in / Default Server Roles
• User-Defined / Customized Server Roles

1. Fixed / Built-in / Default Server Roles: Microsoft SQL Server comes up with 9 fixed or default server roles. DBA or Database security admin can use these logins to provide logins created on the server.

• sysadmin
• bulkadmin
• dbcreator
• diskadmin
• processadmin
• securityadmin
• serveradmin
• setupadmin
• public

SYSADMIN:

• SYSADMIN is the most powerful server role in SQL Server. This role gives to login a server owner level permissions and login with this role free to perform any operation or activity.
• SYSADMIN role detrudes the security checks and restrictions made at login levels.
• SYSADMIN role can manage other server roles both fixed or user-defined.
• SYSADMIN can perform all actions other roles can perform and more.
• SYSADMIN should be controlled and provided to restrict people only. I have seen none other than DBA have SYSADMIN roles in production environment also in some of the restricted environments even only high skills senior DBAs have SYSADMIN permissions rest of junior DBAs will have lower privileges.
• Login with SYSADMIN not needed direct access on the database, SYSADMIN role super seed DB level access. Login can manage DB level users & objects.

BULKADMIN:

• Bulk insert operations are inserting data from files to SQL server tables and vice versa. Bulk admin had the privilege to deals with files laying outside of SQL Server.
• Bulk admin access provides permission to execute bulk command further access on tables & files at the operating system will also be required.
• Bulk admin roles play important while working with ETL (Extract, Transform, and Load) tools.
• BCP is a utility given by Microsoft to use Bulk insert.

DBCREATOR:

• dbcreator as the name implies, it gives the privilege to create a database on SQL instance.
• Useful for an application that needs to create new DB on a timely basis or for developers to create new DBs in the NON PROD server.
• Login who creates a database will become the owner of the database by default. Login will be able to perform any operation DB as being DB Owner.

DISKADMIN

• Legacy server role and still available but used very rarely
• Login with diskadmin role can manage backup device

PROCESSADMIN

• One more rarely used server role
• Login with this role can manage sessions running at the SQL server. Login can terminate any user sessions.
• DBA should think before providing this role as untrusted or fewer experience individuals can cause issues with currently running processes at SQL Server

SECURITYADMIN

• Logins with securityadmin server role can manage logins (Alter \ Disable \ Drop \ Change Password for SQL Logins)
• Logins with this role can grant Control Server Permissions
• These logins can manage database users and securable only in case having direct access to DB.
• Rarely used but need to be careful while assigning
• Cannot change permissions or password of sysadmin role logins

SERVERADMIN

• Powerful role nearer to SYSADMIN
• Login with serveradmin server role can manage server-level configurations
• Usually, DBA with sysadmin role manage server-level configuration so seen very rarely in used
• Login with this role can SHUTDOWN the SQL Server, so double check before assigning this role
• Members of this role can create \ alter endpoints

SETUPADMIN

• Logins with setupadmin can create\alter\drop linked servers
• Very rarely used

PUBLIC

• Every login is part of public server role by default
• You cannot add or remove any login to public server role
• Permission assigned to public will automatically be inherited to logins assigned
• You can change permissions of public role

2. User-Defined / Customized Server Roles: User-Defined Server roles were introduced in SQL Server 2012. In case a fixed /default server roles are not sufficient for your environment, you can create a user-defined server role. User-defined server roles/customer server roles can be created using SQL Server management studio or TSQL.

• What you can grant to User-Defined Server Roles?

You can grant only server-level (ENDPOINT, LOGIN, SERVER, AVAILABILITY GROUP, SERVER ROLE) to user-defined server roles. You can execute sys.fn_builtin_permission to list down all permissions. You can create a role with extra permissions over fixed role or can create a role with no fixed role permissions and only specific permissions

• How to create a Server Role using SQL Server Management Studio?

Connect to SQL Server Database Engine > Security > Server Roles > Right Click > New Server Role
Enter Server Role Name Select permissions you want to give to new role from securable
Add Login to whom you want add under this new server role
Select if you want to give permissions of any fixed role to new role, click OK and Server Role is created.
New User-Defined Server role is listed with fixed server role

• How to create a Server Role using T-SQL?

USE [master]

GO

–CREATE USER-DEFINED SERVER ROLE

CREATE SERVER ROLE [TestServerRole]

GO

–ADD LOGIN AS A MEMBER OF THIS SERVER ROLE

ALTER SERVER ROLE [TestServerRole] ADD MEMBER [Test]

GO

–ASSIGN PERMISIONS

GRANT ALTER ON ENDPOINT::[Dedicated Admin Connection] TO [TestServerRole]

GO

GRANT ALTER ON LOGIN::[sa] TO [TestServerRole]

GO

–ADD PERMISSIONS OF FIXED ROLE TO NEW ROLE

ALTER SERVER ROLE [serveradmin] ADD MEMBER [TestServerRole]

• Script to list permissions of customized roles