Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

Create SQL Server Roles

by suresh

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

Create SQL Server Roles 1

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

Create SQL Server Roles 2

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.
Create SQL Server Roles 3

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.

Create SQL Server Roles 4

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

Create SQL Server Roles 5

If you know the name, write the name in the textbox and click Check Names button

Create SQL Server Roles 6

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

Create SQL Server Roles 7

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

Create SQL Server Roles 8

Click OK to close this window

Create SQL Server Roles 9

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

Create SQL Server Roles 10

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

Create SQL Server Roles 11

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

Create SQL Server Roles 12

Now you can see our new Role member.

Create SQL Server Roles 13

Within the Memberships tab, you can assign one or more default Server Roles to this Role.

Create SQL Server Roles 14

For now, we are selecting all the roles.

Create SQL Server Roles 15

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

Create SQL Server Roles 16

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

OUTPUT

Create SQL Server Roles 19

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

Create SQL Server Roles 20

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.

Create SQL Server Roles 21

Let me provide the Owner as John

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

OUTPUT

Create SQL Server Roles 22

You can see the new server role.

Create SQL Server Roles 23

From the properties, you can see the owner as John

Create SQL Server Roles 24

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.

Create SQL Server Roles 17

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

Create SQL Server Roles 18

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

OUTPUT

Create SQL Server Roles 25

You can see the updated server role.

Create SQL Server Roles 26

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

OUTPUT

Create SQL Server Roles 27

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

OUTPUT

Create SQL Server Roles 28

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

Create SQL Server Roles 29

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

OUTPUT

Create SQL Server Roles 30

you can find the same in properties.

Create SQL Server Roles 31

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

OUTPUT

Create SQL Server Roles 32

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

Create SQL Server Roles 33

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

Create SQL Server Roles 34

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

OUTPUT

Create SQL Server Roles 35

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

Create SQL Server Roles 36

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy