How to Create Server Roles in SQL Server ?

Leave a comment (3) Go to comments

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

How to Create Server Roles in SQL Server ? sql server denali Create Server Roles SQL Server 2011 Create Server Roles Create Server Roles in SQL Server CREATE SERVER ROLE AUTHORIZATION Create Fixed Server Role in SQL Server

  • Select the New Server Role option, where you can Give Role name and selected permissions to that role.

How to Create Server Roles in SQL Server ? sql server denali Create Server Roles SQL Server 2011 Create Server Roles Create Server Roles in SQL Server CREATE SERVER ROLE AUTHORIZATION Create Fixed Server Role in SQL Server

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.

How to Create Server Roles in SQL Server ? sql server denali Create Server Roles SQL Server 2011 Create Server Roles Create Server Roles in SQL Server CREATE SERVER ROLE AUTHORIZATION Create Fixed Server Role in SQL Server

 

Note : user-defined server role can be a member of all FIXED server roles except the sysadmin.

EOF - How to Create Server Roles in SQL Server ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

3 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: