Create SQL Server Roles

Step by step approach to create SQL Server Roles using SQL Server Management Studio, and Transact SQL query. Before we start configuring new Server Roles, let me show you the list of default Server Roles in our SQL Server Management Studio

Following are the list of Server Roles available in SQL Server

bulkadmin: Member of this role can run the Bulk Insert Statements.
dbcreator: Member of this role can Create, Alter, Restore, and Drop any Database in SQL.
diskadmin: The disk admin serer roles are used to manage disk files.
processadmin: This server role can end all the running processes in an SQL Instance.
public: By default, every login belongs to Public Server Role.
securityadmin: Member of this role Grant, Deny, Revoke both Server level permission and Database level permissions. They can also reset all SQL Server Logins.
serveradmin: The server admin members can shut down the server, or they can change the server-wide configurations.
setupadmin: Members with this server role can remove or add linked servers using the Transact-SQL queries.
sysadmin: Member can perform any activity in the server. This member bypasses any restrictions provided by others too.

Create SQL Server Roles using SSMS

To Create Server Roles, please expand the Server Roles folder, and right-click on the SQL Server Roles folder to open the context menu. Please select the New Server Role.. option

Once you select the New Server Role.. option, the following window will open.

Server role name: Please use a unique name. By seeing the name, you have to understand its functionality.
Owner: You have to specify the owner name. This person can change this role as per his/her requirements.
Securables: Please specify the securable that this role has to handle. For example, you use the Endpoints, or logins, etc.

As you can see from the below screenshot, we assigned udr_MasterServerRole as the server role name. Let me click on the … (Browse) button to select the Owner. Please refer to Create Windows Logins, or Create SQL Server Logins to understand the steps involved in creating logins.

Once you click on the …. button, the following window opened. If you know the name, write the name in the textbox and click Check Names button

As you can notice, it has automatically recovered the account information. If you don’t remember the name, click the Browse button.

It showcases all the existing users. Please pick the User from this list.

Click OK to close this window

For now, we are selecting the Server roles as the securable

Within the Members tab, you can add members to this server role. Something like adding your Team members.

Use the above-specified technique to add members. As you can see from the below screenshot, we are adding Mahesh login.

Now you can see our new Role member.

Within the Memberships tab, you can assign one or more default Server Roles to this Role. For now, we are selecting all the roles.

From the below screenshot, you can see our newly created Server Role in SQL Server Management Studio.

Create SQL Server Roles using CREATE SERVER Role

The SQL Server provides a CREATE SERVER ROLE command to create a new server role. The below code snippet shows the syntax to Create SQL Server Roles.

-- Syntax to Create New SQL Roles 
USE [master]
GO
CREATE SERVER ROLE [Role Name] [ AUTHORIZATION User_Name];
GO

Below code snippet will create a new server role udr_SecurityAdmin.

-- Create New SQL Roles
USE [master]
GO
CREATE SERVER ROLE [udr_SecurityAdmin];
GO

Run the above create server role query

Messages
--------
Command(s) completed successfully.

From the following screenshot, see our newly created Server role in object explorer.

Please go to its properties to edit or view the role settings. By default, it has assigned the current user as the owner because we haven’t provided the information about the owner in our statement.

Let me provide the Owner as John

-- Create New SQL Roles
USE [master]
GO
CREATE SERVER ROLE [udr_dbcreator] 
        AUTHORIZATION John;
GO

Run the above create server role query

Messages
--------
Command(s) completed successfully.

You can see the new server role.

From the properties, you can see the owner as John

Edit SQL Server Roles using SSMS

To edit the existing SQL Server Roles, Please go to the Server Roles folder to select the required Role name. Right-click on the Role that you want to edit and select Properties option from the context menu.

Once you select the Properties, the following window opened. Use this to alter the Owner, Securables, Members, and memberships.

Edit SQL Server Roles using T-SQL

Use ALTER SERVER ROLE command to edit the server role properties. Below code snippet will rename the udr_SecurityAdmin to udr_NewSecurityAdmin

-- Create New SQL Roles
USE [master]
GO
ALTER SERVER ROLE [udr_SecurityAdmin]
WITH NAME = [udr_NewSecurityAdmin] 
GO

Execute alter server role query

Messages
--------
Command(s) completed successfully.

You can see the updated server role.

Add Role Members

Below code snippet will add new SQL member to existing Server Role: udr_NewSceirutyAdmin

USE [master]
GO
ALTER SERVER ROLE [udr_NewSecurityAdmin]
ADD MEMBER [Mahesh];
GO

Run add member to server role query

Messages
--------
Command(s) completed successfully.

Below code will add new Windows login [PRASAD\Dave] to existing Server Role : udr_NewSceirutyAdmin

-- Create New SQL Roles
USE [master]
GO
ALTER SERVER ROLE [udr_NewSecurityAdmin]
ADD MEMBER [PRASAD\Dave];
GO

Run the above new windows login query

Messages
--------
Command(s) completed successfully.

From the properties, you can see the new Role members that we added before.

Drop Role Members

Below code will remove the PRASAD\Dave role member from the udr_NewSecurityAdmin

-- Create New SQL Roles
USE [master]
GO
ALTER SERVER ROLE [udr_NewSecurityAdmin]
DROP MEMBER [PRASAD\Dave];
GO

Execute drop role members query

Messages
--------
Command(s) completed successfully.

you can find the same in properties.

You can also grant permission using the query. We will write a dedicated article about the permissions.

-- Create New SQL Roles
USE [master]
GO
GRANT ALTER ON 
	SERVER ROLE::[udr_NewSecurityAdmin] TO [PRASAD\Dave];  
GO

Run grant permissions query

Messages
--------
Command(s) completed successfully.

Delete SQL Server Roles using SSMS

To delete the existing Server Roles, Please expand the Server Roles folder to select the required role name. Right-click on the role name that you want to delete, and select Delete option

Once you select the Delete option, the following window will open. Click OK to delete the server role.

Delete SQL Server Roles using DROP Server Role

The SQL Server provides DROP SERVER ROLE command to delete or drop the existing roles

-- Create New SQL Roles
USE [master]
GO
DROP SERVER ROLE [udr_dbcreator]; -- This is Role Name
GO

Run drop server role query

Messages
--------
Command(s) completed successfully.

Now you can see there is no server role with name udr_dbcreator