Create SQL Server Roles

In this article we will show you the step by step approach to create SQL Server Roles. There are two approaches to create Server Roles: SQL Server Management Studio, and Transact SQL query. This article explains both these approaches with example. 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 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 diskadmin serer roles is 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 serveradmin 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 bypass any restrictions provided by others too.

Create SQL Server Roles using SSMS

In order 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 as we shown below

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

Server role name: Please use 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 own requirements.
Securables: Please specify the securables 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 Create Windows Logins, or Create SQL Server Logins to understand the steps involved in creating logins.

Once you click on the …. button, following window will be opened.

If you know the name then write the name inside the text box and click Check Names button

As you can see, it has automatically retrieved the account information. If you don’t know the name, click Browse button.

This will display all the existing users. Please select the User from this list.

Click OK to close this window

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

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 T-SQL

The SQL Server provides CREATE SERVER ROLE command to create a new server role. below code snippet will show you the syntax to create SQL 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.

SQL CODE

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

OUTPUT

From the below screenshot you can see our newly created Server role in object explorer.

Please go to its properties to see, or edit the role settings. As you can see, by default it has assigned the current user as the owner. This is because, we haven’t provided the information about the owner in our statement.

Let me provide the Owner as John

SQL CODE

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

OUTPUT

You can see the new server role.

From the properties you can the owner as John

Edit SQL Server Roles using SSMS

In order to edit the existing 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 Properties option, following window will be opened. Use this window 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

SQL CODE

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

OUTPUT

You can see the updated server role.

Add Role Members

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

SQL CODE

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

OUTPUT

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

SQL CODE

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

OUTPUT

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

SQL CODE

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

OUTPUT

you can find the same in properties.

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

SQL CODE

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

OUTPUT

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 from the context menu as we shown below.

Once you select Delete option from context menu, below shown window will be opened. Click OK to delete the server role.

Delete SQL Server Roles using T-SQL

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

SQL CODE

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

OUTPUT

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

Thank You for Visiting Our Blog