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
Comments
Post a Comment