Create a user-defined server role in SQL Server 2012 with T-SQL, SSMS

What permissions can be granted to a user-defined server role?

Execute the query below to list the permissions that can be granted to a user-defined server role in SQL Server 2012 and later editions.

USE master
GO

SELECT * FROM sys.fn_builtin_permissions(DEFAULT)            
             WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE')            
              ORDER BY class_desc, permission_name
GO

Figure 1: List permissions

Create SQL Server login

The first step toward building a new user-defined server role is to create or add a new login, which can then be assigned to a new user-defined server role. You can create a new SQL Server login by executing the T-SQL query below.

USE master
GO

CREATE LOGIN [Brinto] WITH PASSWORD = 'B[email protected]',
               DEFAULT_DATABASE = [master],
               CHECK_EXPIRATION=OFF,
               CHECK_POLICY=OFF
GO

Figure 2: Creating a new SQL Server login

Create user-defined SQL Server role in SSMS

Once the login is successfully created, the next step is to create a user-defined SQL Server role using SQL Server Management Studio (SSMS) or T-SQL code. Using SSMS, connect to a SQL Server 2012 instance and open Object Explorer.

Expand the Security folder and right-click the Server Roles folder and choose the New Server Role… option from the drop-down menu.

In the General Page of the New Server Role window, enter an appropriate server role name. In the Owner box, enter the server principal who will own the New Server Role.

Under Securable, select one or more server-level securables. Once a securable is selected, you will be able to grant or deny permissions on the securable.

Under the Permissions: Explicit box, select the check box to GRANT, WITH GRANT or DENY permission to this server role for the selected securables. In this case, I chose ALTER TRACE, CONNECT SQL, CREATE ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION and VIEW SERVER STATE permissions for this server role.

Figure 3: Selecting permissions on the General page for the new server role

On the Members page, use the Add… button to add SQL Server logins, Windows accounts and Windows groups to this new server role. In this demo, I chose the newly added SQL Server login.

Figure 4: Using the Members page to add new role members to the new server role.

On the Members page, select the check box to make the new user-defined server role a member of a fixed server role. Finally, click OK to create a user-defined server role in SQL Server 2012.

Figure 5: Creating the user-defined server role on the Members page.

Once the user-defined server role is successfully created, it will be available under Server Roles.

Figure 6: The new server role under the Server Roles folder.

Create user-defined SQL Server roles using T-SQL query

To create a user-defined SQL Server role using T-SQL, execute the T-SQL code below.

USE [master]
GO

CREATE SERVER ROLE [JuniorDBA] AUTHORIZATION [sa]
GO

ALTER SERVER ROLE [JuniorDBA] ADD MEMBER [Brinto]
GO

Figure 7: Creating a user-defined SQL Server roll using T-SQL.

Grant permissions to user-defined SQL Server roles using T-SQL query

Execute the T-SQL code below to add respective permissions to the user-defined server role created using the above T-SQL code. I chose to grant the permissions ALTER TRACE, CONNECT SQL, CREATE ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION and VIEW SERVER STATE to the sample role.

USE [master]
GO

GRANT ALTER TRACE TO [JuniorDBA];
GRANT CONNECT SQL TO [JuniorDBA];
GRANT CREATE ANY DATABASE TO [JuniorDBA];
GRANT VIEW ANY DATABASE TO [JuniorDBA];
GRANT VIEW ANY DEFINITION TO [JuniorDBA];
GRANT VIEW SERVER STATE TO [JuniorDBA];

Figure 8: Assigning permissions with T-SQL.

Verify permissions

Quickly verify permissions assigned to the newly created server role by executing the T-SQL query below in a new query window. Since the user has VIEW SERVER STATE permissions, you can get the result from the dynamic management view.

SELECT SUSER_SNAME()
EXECUTE AS LOGIN = 'Brinto'

SELECT SUSER_SNAME()
SELECT * FROM sys.dm_os_windows_info

REVERT
SELECT SUSER_SNAME()

Figure 9: Verifying permissions for the new SQL Server role and viewing them in the dynamic management view