Custom Server Roles in SQL Server Denali

One of the new features in Denali is the ability to create custom server roles. This can be done using the CREATE SERVER ROLE and ALTER SERVER ROLE commands. You can also do it using the GUI. I’m by no means an advocate of doing things with the GUI (quite the contrary), but it is a great way to explore a new feature and see how it can be done via T-SQL.

So this is what I did. I decided to create a server role for administering database mirroring just to test the feature and see how it scripted out. I set up a mirrored database (from SQL Server 2008 R2 to SQL Server Denali) and set about creating a role. I named my new server role svr_MirrorAdmin.

I right-clicked on the Server Roles node in Object Explorer and clicked New Server Role. This opened the dialog box for a new server role. On the General tab, I named the server role and set the owner as ‘sa’. I expanded the Endpoints node under Securables, and checked the endpoint name Mirroring. At the bottom under Permissions for Mirroring, I checked the CONTROL permission. If I had multiple endpoints selected, and I wanted to grant the same level of permissions for all selected endpoints, I could do so by checking all appropriate endpoints and highlighting the Endpoints level under securables instead of a specific endpoint. The bottom section would then display Permissions for Selected Endpoints. I also selected the Availability Groups securable in the Securables section, but since no availability groups exist yet, I was not able to define and permissions and none were set for Availability Groups.

On the Members tab, I added my login as a member of the new server role. On the Memberships tab, I could have selected to make this role a member of another server role, but I opted to make no changes to this tab. If I had added the role to an existing server role, it would have scripted it as ALTER SERVER ROLE [server role] ADD MEMBER [svr_MirrorAdmin].

The images below shows how I configured the dialog for creating the new server role. The code below that demonstrates the DDL language that it generated when I scripted the server role to a new query window instead of saving it.

USE [master]
GO

CREATE SERVER ROLE [svr_MirrorAdmin] AUTHORIZATION [sa]
GO

ALTER SERVER ROLE [svr_MirrorAdmin] ADD MEMBER [SQLSoldiersqlsoldier]
GO

use [master]
GO

GRANT CONTROL ON ENDPOINT::[Mirroring] TO [svr_MirrorAdmin]
GO