Can we create user defined Server Roles in SQL Server ?
YES, we can create SQL Server Roles in SQL Server (2011) Denali onwards. Starting from SQL Server Denali you can create user-defined server roles and add server-level permissions to the user-defined server roles. We can not create user defined roles in any prior release which includes (SQL Server 2008 R2, SQL Server 2008, 2005,2000,7.0).
What are the benefits of creating user defined Server Roles in SQL Server ?
This Option gives more flexibility in terms of managing security for DBAs. Now DBA’s can delegate some controls to Junior resource easily.
How to create User defined Server Role in SQL Server ?
Creating user defined server roles is quite simple, we can do this with SSMS GUI and Transact SQL.
Creating User defined Server Role in SQL Server using TSQL
USE [master] GO -- Creating User-defined server role JrDBA CREATE SERVER ROLE [JrDBA] AUTHORIZATION [sa] -- Granting view Server State for JrDBA on TEST login GRANT VIEW SERVER STATE TO [JrDBA]
Creating User defined Server Role in SQL Server via SQL Server Management Studio
- SSMS >>> SQL Server >>> Security >>> Right Click and you will a option to create a new Server Role, as shown in screen shot
- Select the New Server Role option, where you can Give Role name and selected permissions to that role.
you can even select the members directly from here.
- Once you click OK, you will be able to see the same role in Server role list as shown in screen shot.
Note : user-defined server role can be a member of all FIXED server roles except the sysadmin.