Custom Server Roles in SQL Server Denali :)

Custom Server Roles! Yes, this is a brand new enhancement Microsoft has provided in SQL Denali.
Upto SQL Server 2008R2, We only had flexibility for creating Custom Database Roles. But the Server roles were fixed, which comes along with your installation.

So, Let’s peak on how to create a custom Server Role and assign Server Scoped Permissions in Denali!!
Pretty Simple Using GUI:
Scenario: Let’s assume your team hired a Level2 DBA who needs supervision for few weeks/months. You don’t want to grant him sysadmin privileges where he can do what ever he wants. You really don’t want a Jr DBA connecting to your SQL Server and stop/Start/Restart SQL Services. So let’s assume your plan is just to limit his permissions to Create/Alter any Database, Alter Trace – for Running Traces, Alter Logins/Connections/Linked Servers. What can be done with less pain is Just create a Server role with desired permissions and add him/her as its member.( you can use your own imagination for Server Scope Permissions, for example…if you want him/her to control DB Mirroring, consider granting perms on Mirroring ENDPOINTS and so on….). You can Just add all your newly joined DBA’s to this Role which is Just a matter if Single Button click 🙂
In my Example, I’m creating a Server Role called as “Lev2DBA” and choosing Server as a SECURABLE and granting permissions as per my requirement.
Snap1:

Snap 2:

Now, go to Members Tab and Add that Level2 DBA(login “test” in my case) to this Role as shown below.

Note: You can also directly add this to any existing Fixed Server Role directly from “Memberships” tab as shown below.

If you’ve observed thoroughly,by this time you might’ve noticed that in the above Screenshot there is no “SYSADMIN” role. I believe MSFT is not allowing us to add these members to SYSADMIN role. ( Not sure, why they are allowing ServerAdmin in that case!!!)

It’s also pretty Simple from T-SQL: You can follow the below Syntax for achieving the same.

USE [master]
GO
CREATE SERVER ROLE [Lev2DBA] AUTHORIZATION [sa]
GO
ALTER SERVER ROLE [Lev2DBA] ADD MEMBER [test]
GO
GRANT ALTER ANY CONNECTION TO [Lev2DBA]
GO
GRANT ALTER ANY DATABASE TO [Lev2DBA]
GO
GRANT ALTER ANY LINKED SERVER TO [Lev2DBA]
GO
GRANT ALTER ANY LOGIN TO [Lev2DBA]
GO
GRANT ALTER TRACE TO [Lev2DBA]
GO
GRANT CREATE ANY DATABASE TO [Lev2DBA]
GO