Server roles have been around since before I started working with SQL Server however these roles have always been strictly defined and there has been no ability to either alter them or create new ones. SQL Server 2012 finally changes that and provides you with the opportunity to create server level roles that will let you provide greater permissions to logins without having to constantly deal with the minutiae of managing them on a login by login basis.

Let’s say for example you wanted to grant several logins the ability the view any definition. Normally this would require you to perform those grants on a very granular level. Now you can just create a server role and drop logins into that role:

USE [master]

GO

CREATE SERVER ROLE [SrvViewDefs]

GO

use [master]

GO

GRANT VIEW ANY DEFINITION TO [SrvViewDefs]

GO

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsqlJoe];

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsqlBiggles];

That’s some quick and easy management.

Here’s some examples of other permissions that can be quickly and easily provisioned to multiple users via user defined server roles:

• Alter any linked server
• Alter any login
• Alter any server audit
• Alter any server role
• Alter resources
• Alter server state
• Alter settings
• View any database
• Shutdown SQL Server

It goes beyond basic SQL Server permissions, you can also allow server roles to impersonate logins or manage Availability Groups

USE [master]

GO

CREATE SERVER ROLE [SrvImpersonate]

GO

use [master]

GO

GRANT IMPERSONATE ON LOGIN::[SIRSQLnic] TO [SrvImpersonate]

GO

ALTER SERVER ROLE [SrvImpersonate] ADD MEMBER [sirsqlBiggles];

Anything that allows me to manage SQL Server more efficiently makes me happy. User defined server roles certainly make me happy.