Monday, 22 July 2013

Creating and Managing User-Defined Roles

What Permissions Can Be Assigned to user-define roles?

Before we start, we first look at what permission can be assigned to user-defined roles. To do that, execute the following SQL Server query:
USE [master]  GO SELECT * FROM sys.fn_builtin_permissions(DEFAULT)  WHERE [class_desc] IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE')  ORDER BY [class_desc], [permission_name] GO

Creating user-defined roles with T-SQL

 

You can use CREATE SERVER ROLE statement to create user-defined server role as shown in the following listing in which I’m creating user-defined server role called ApplicationDBA using this statement:
USE [master] GO CREATE SERVER ROLE [ApplicationDBA] GO
However, this group cannot control the server (see below):
USE [master] GO --Granting server-wide permissions GRANT CREATE ANY DATABASE TO [ApplicationDBA] GRANT VIEW ANY DATABASE TO [ApplicationDBA] GRANT VIEW ANY DEFINITION TO [ApplicationDBA] GRANT VIEW SERVER STATE TO [ApplicationDBA] GRANT ALTER ANY LOGIN TO [ApplicationDBA] GO --Denying server-wide permissions DENY CONTROL SERVER TO [ApplicationDBA] GO

Adding members for user-defined roles

You can use ALTER SERVER ROLE to add members to user-defined role (see below):
USE [master] GO ALTER SERVER ROLE [ApplicationDBA] ADD MEMBER [DevelopmentDBA] GO ALTER SERVER ROLE [ApplicationDBA] ADD MEMBER [IITCUK\SQLAdministrator] GO

Making user-defined role member of fixed server roles

We can also make your user-defined role member of any existing server role. For example in the code below I’m adding ApplicationDBA server role to dbcreator fixed server role(see below):
USE [master] GO ALTER SERVER ROLE [dbcreator] ADD MEMBER [ApplicationDBA] GO
To drop user-define role we can user DROP SERVER ROLE statement as follow:
USE [master] GO DROP SERVER ROLE [ApplicationDBA] GO