
carloscastilla - Fotolia
Create a user-defined server role in SQL Server 2012 with T-SQL, SSMS
Ashish Mehta shows two ways – with T-SQL and with SSMS -- to make a user-defined server role in SQL Server 2012.
Creating user-defined server roles and assigning server-level permissions are two features that were introduced in SQL Server 2012. This article examines a sample use case of a user-defined server role for junior DBAs.
User-defined server roles are created using SQL Server Management Studio or T-SQL code. You can add server-level principals, such as SQL Server logins, Windows accounts and Windows groups to user-defined server roles in SQL Server 2012 and later. You can then specify explicit permissions to members.
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 = '[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.

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.

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.

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