Demo Transcript: Customizing Server Roles in SQL Server 2012

SQL11UPD02-TSCRIPT-06

This wiki is a transcript of a previously recorded video.

Customizing Server Roles in SQL Server

Hey everybody, this is Joe Sack – I’m a Principal Consultant with SQLskills, and for this demo we’re going to demonstrate customizing server roles in SQL Server 2012.

So one of the key scenarios for this new functionality is separation of duties. Prior to SQL Server 2012, your options were to add people at the server level, the SQL Server instance level, add them to fixed server roles, so for example sysadmin, which would often be way more, in terms of permissions, than you would want to add them to. The other option would be if you had a set of logins, you could add them and give them more granular permissions, but you’d have to do it one-by-one. So there was no way to group it, and in terms of the manageability story, it wasn’t as efficient.

So SQL Server 2012 now offers the server roles that you can add that are customizable and what I’m going to do is set up two logins. We’ve got Jane and Annie. And imagine, if you will, a DBA Tier A, a DBA Tier B – so you’ve got different subsections of a DBA team. And in this case I’m going to add DBA_Tier_A and the syntax is very straightforward – CREATE SERVER ROLE and the name of the server role and the owner of that server role.

Alright, so we’ve got a DBA_Tier_A and I can confirm it in sys.server_principals. So this was around in previous versions and I can check – and yep, it’s a principal just like anything else – it’s a principal that I can assign permissions to.

And now I’m going to add both Jane and Annie to that server role. In this case ALTER SERVER ROLE … ADD MEMBER. And then I can validate the actual role members through server_role_members - so that catalog view is new – and then the existing, in prior versions, sys.server_principals – I’m going to join to that and I see that Jane and Annie are members of that role.

Alright, so another straightforward example is just dropping a member. So I don’t want Annie to be part of that role any more, it’s just ALTER SERVER ROLE, the name of the role, and then DROP MEMBER and the name of the principal that I’m dropping. So I’m dropping Annie.

And then of course, all I’ve done is added users and then removed a user, removed a login or principal from that role, but you’re going to want to add permissions as well. So in this example I’m adding two different permissions – I’m granting ALTER ANY LOGIN to that role and I’m granting VIEW SERVER STATE to that role as well.

And then another example is renaming it. So let’s say DBA_Tier_A should have been DBA_Tier_B and let’s also say that I added far more than I did right here – so I’ve several permissions that I took time to add – I don’t have to drop and recreate it from scratch. I can just rename it and in this case I just renamed it from A to B, just through ALTER SERVER ROLE … WITH NAME.

And I can confirm that indeed that one user was left in that role, Jane is still there, and confirming that through server_role_members and server_principals. And then I can also see that permissions that I assigned to it before I renamed it persisted as well.

And just last thing is if you want to drop a server role, it’s DROP SERVER ROLE. I’m going to drop both logins that I used for this example and then I’ll drop the server role right here.

So that was just a real brief demonstration on server role functionality and thanks for watching.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)