User Defined Server Role on SQL Server 2012

Prior to Microsoft SQL Server 2012, we had no easy a way to create a server level role and assign permissions to it. We could only add a login as member to existing server roles or give server level permissions individually, one by one, to a login. This changed in SQL Server 2012 with the introduction of the feature, User Defined Server Role.

Just like creating a database role on a database with list of permissions assignment, we can now create user defined server level roles.

Let us consider this example.

If I need to give view activity monitor, view definition of objects permissions to all the databases and view any database permissions to three logins, I have to do the following.

Prior to SQL Server 2012, I have to do the following in order to achieve this.

USE [master]
GO
GRANT VIEW ANY DEFINITION to [Login2]
GO
GRANT VIEW SERVER STATE to  [Login2]
GO
GRANT VIEW ANY DATABASE to  [Login2]
GO
GRANT VIEW ANY DEFINITION to [Login3]
GO
GRANT VIEW SERVER STATE to  [Login3]
GO
GRANT VIEW ANY DATABASE to  [Login3]
GO
GRANT VIEW ANY DEFINITION to [Domain\Login1]
GO
GRANT VIEW SERVER STATE to  [Domain\Login1]
GO
GRANT VIEW ANY DATABASE to  [Domain\Login1]
GO
 

Here, basically we have to repeat the same set of GRANT commands for every login that we want to give access.

In SQL Server 2012, you can create a user defined server role with give view activity monitor, view definition of objects permissions to all the databases and view any database permissions permissions and then add the users to that server role as members.

Let us create the server role ServerReadOnly role as shown below.

USE [master]

GO

CREATE SERVER ROLE [ServerReadOnly]

GO

GRANT VIEW ANY DATABASE TO [ServerReadOnly]

GO

GRANT VIEW ANY DEFINITION TO [ServerReadOnly]

GO

GRANT VIEW SERVER STATE TO [ServerReadOnly]

GO

Now let us assign the three logins to this role.

USE [master]
GO
ALTER SERVER ROLE [ServerReadOnly] ADD MEMBER [Login2]
GO
ALTER SERVER ROLE [ServerReadOnly] ADD MEMBER [Login3]
GO
ALTER SERVER ROLE [ServerReadOnly] ADD MEMBER [Domain\Login1]
GO

We can categorize server level roles based on the functionality and assign logins to it. Let us consider giving all levels of access related to Create/Audit Audit, Trace files

We could create a dedicated role for the auditing and tracing as shown below.

USE [master]
GO
CREATE SERVER ROLE [ServerTraceRole]
GO
 

Now let us assign all the Audit, Trace related permissions to the role as shown below.

use [master]
GO
GRANT ALTER ANY SERVER AUDIT TO [ServerTraceRole]
GO
use [master]
GO
GRANT ALTER TRACE TO [ServerTraceRole]
GO
use [master]
GO
GRANT CREATE TRACE EVENT NOTIFICATION TO [ServerTraceRole]
GO

Now let us assign the logins to this role as shown below.

use [master]
GO
ALTER SERVER ROLE [ServerTraceRole] ADD MEMBER [Login5]
GO
ALTER SERVER ROLE [ServerTraceRole] ADD MEMBER [Login6]
GO
ALTER SERVER ROLE [ServerReadOnly] ADD MEMBER [Domain\Login7]
GO

You could also use the graphical user interface on the SQL Server Management Studio to create the user defined server role.

Create user defined server role
Create user defined server role

Server role name
Server role name

You can add the following list of server level permissions to the user defined server role.

Server permission

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY CONNECTION
  • ALTER ANY CREDENTIAL
  • ALTER ANY DATABASE
  • ALTER ANY ENDPOINT
  • ALTER ANY EVENT NOTIFICATION
  • ALTER ANY EVENT SESSION
  • ALTER ANY LINKED SERVER
  • ALTER ANY LOGIN
  • ALTER ANY SERVER AUDIT
  • ALTER ANY SERVER ROLE
  • ALTER RESOURCES
  • ALTER SERVER STATE
  • ALTER SETTINGS
  • ALTER TRACE
  • AUTHENTICATE SERVER
  • CONNECT SQL
  • CONTROL SERVER
  • CREATE ANY DATABASE
  • CREATE DDL EVENT NOTIFICATION
  • CREATE ENDPOINT
  • CREATE SERVER ROLE
  • CREATE TRACE EVENT NOTIFICATION
  • EXTERNAL ACCESS ASSEMBLY
  • SHUTDOWN
  • UNSAFE ASSEMBLY
  • VIEW ANY DATABASE
  • VIEW ANY DEFINITION
  • VIEW SERVER STATE

You can add the following list of ENDPOINT related permissions to the user defined server role.

  • [Dedicated Admin Connection]
  • [TSQL Named Pipes]
  • [TSQL Local Machine]
  • [TSQL Default VIA]
  • [TSQL Default TCP]

Example:

		ALTER ON ENDPOINT::[Dedicated Admin Connection]
		CONNECT ON ENDPOINT::[Dedicated Admin Connection]
		CONTROL ON ENDPOINT::[Dedicated Admin Connection]
		TAKE OWNERSHIP ON ENDPOINT::[Dedicated Admin Connection]
		VIEW DEFINITION ON ENDPOINT::[Dedicated Admin Connection]

You can add the following list of login level permissions to the user defined server role.

  • [LOGIN]

Example:

ALTER ON LOGIN::[Login1]
CONTROL ON LOGIN::[Login1]
IMPERSONATE ON LOGIN::[Login1] 
VIEW DEFINITION ON LOGIN::[Login1]

Conclusion:

As mentioned in the beginning of this article, just like creating the database role on a database with list of permissions assignment, we can now create user defined server level roles.

See all articles by MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles