Create SQL Server Roles

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

Create SQL Server Roles using SSMS

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

Once you select the New 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 as per his/her requirements.
Securables: Please specify the securable that this 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 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 SQL Select Server Login or Roles 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. For now, we are selecting all of them.

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

SQL Server CREATE SERVER Role

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

USE [master]
GO
CREATE SERVER ROLE [Role Name] [ AUTHORIZATION User_Name];
GO

Below code snippet will create a new server role udr_SecurityAdmin.

USE [master]
GO
CREATE SERVER ROLE [udr_SecurityAdmin];
GO

Run the above 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

USE [master]
GO
CREATE SERVER ROLE [udr_dbcreator] 
        AUTHORIZATION John;
GO

Run the above query

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

You can see the new one.

From the properties, you can see the owner as John

Edit SQL Server Roles using SSMS

To edit the existing one, Please go to the SQL Server Roles folder to select the required name. Right-click on the name 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 transact query

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

USE [master]
GO
ALTER SERVER ROLE [udr_SecurityAdmin]
WITH NAME = [udr_NewSecurityAdmin] 
GO

Execute alter query

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

You can see the updated.

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 query

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

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

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

USE [master]
GO
ALTER SERVER ROLE [udr_NewSecurityAdmin]
DROP MEMBER [PRASAD\Dave];
GO

Execute drop 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.

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 name. Right-click on the 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.

SQL Delete or Drop Server Role

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

USE [master]
GO
DROP SERVER ROLE [udr_dbcreator]; -- This is Role Name
GO

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

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