So you’ve heard the buzz by now – SQL Server Denali is now available in a Community Technology Preview. This was announced, released and handed out in DVD form at the SQL PASS Summit this past week.
I just got the chance to install it and I played with one feature that I could see as pretty hand and wanted to share about it here. In the meantime, get your self some Denali. Lots of places blogging about where/how but go right to Microsoft and start downloading CTP #1 now and start playing! Easy installation.
Create Your Own Server Roles
When I had found myself in vanilla production DBA roles, I found that one of the things I hated doing most was repeated security work. I am a huge fan of least privilege so I don’t just grant SA or DBO to every login. That means I had to assign individual permissions to individual users or groups based on the privilege level they require to get the job done. Well I made life easier typically by insisting on going group based and role based. I would have the development teams create and maintain the role creation scripts and I would review and execute. I also liked to force windows authentication where I could do so and then insist on Group based security. I would map the groups the appropriate roles and any server specific permissions necessary for a particular group. Saved time.
One area it was still a pain was at smaller shops where we had support farmed out to various disciplines and I had to grant some server level permissions to support groups or individual users. Not a big deal but I always found myself saying, “I wish I could create a server role like I can create a database role” Well, now I can.
I played with it quickly today and created a support role that I might want to create which would allow a trusted and trained individual to do some checking, reading definitions, looking at databases and running DMVs/server side traces (or even profiler, if in a smaller shop on a less busy system that would be alright to run a quick profile trace on.. I know… I know.. Not a best practice but still sometimes, when tested and when it performs alright, it is an alright tool to use – well filtered – for quickly troubleshooting an issue….).
A sample script I started playing around with is below. I haven’t yet tested if the login assigned can actually use DAC with the permissions I’ve assigned to it but it can at least run DMVs and see what is going on/etc. I also am not sure if I would use View any DB and view any Definition in most environments. I would rather give specific permissions to specific objects or databases if on an instance with a lot of databases.
A simple feature solving more of a minor headache kind of problem but it was all I have time to play with tonight 🙂 Looking forward to more digging in the coming weeks!
USE [master]
GO
CREATE SERVER ROLE [SupportRole]
GRANT CONNECT ON ENDPOINT::[Dedicated Admin Connection] TO [SupportRole]
GRANT VIEW DEFINITION ON ENDPOINT::[Dedicated Admin Connection] TO [SupportRole]
GRANT VIEW DEFINITION ON LOGIN::[sa] TO [SupportRole]
GRANT CONNECT SQL TO [SupportRole]
GRANT VIEW ANY DATABASE TO [SupportRole]
GRANT VIEW ANY DEFINITION TO [SupportRole]
GRANT VIEW SERVER STATE TO [SupportRole]
GO
ALTER SERVER ROLE [SupportRole] ADD MEMBER [NightsAndWeekendsPeeps]
GO
I could then log in as the NightsAndWeekendsPeeps login and have a look at what was happening on the server without having to be in the SA role or have individual permissions assigned to my login or the group it belonged to. Yay for more options for role based permissions at the server level. The list of permissions or secureables that you can add to the role seem to be the full list of server level permissions available to a user or group to manually assign. I can think of other potential reasons to create these, not just support roles, so they should be helpful as long as least privilege doesn’t go out the window. Enjoy The CTP!
2 birds with 1 post…you have also provided what permissions a junior or lower level DBA could be given to see and learn what goes on with the SQL without being able to break anything 🙂
Thanks
You underestimate the power of a lower level DBA, Shawn 😉 I kept mentioning that whole training thing 🙂 Some DMVs can slow things down, cause blocking, performance issues, etc. With a little training and a little, “You ‘understandin what I’m a ‘sayin?” these permissions are good ones to give to a support role, though – indeed. I would include the ability to backup, perhaps modify database level security, etc. Maybe that is another post to write sometime 🙂
Thanks for the post. However, I seem to hit a bit of a dead-end if I try to assign permissions to individual server-level securables. For example, suppose I have a line-of-buisness application that uses a SQL Server login to connect to its database. I want to delegate administration of that application to a small set of application administrators so they can manage the application’s login, but I don’t want them to be able to manage any other logins on the server – seems like a perfect scenario for a user-defined server role, right? However, here’s what happens when I try to run the following Transact-SQL to implement it:
/* ASSUMES SERVER IS CONFIGURED FOR “Mixed Mode” (Windows And SQL Server) Authentication */
— Execute this code as a login who is a member of the sysadmin fixed server role
USE master
GO
— Create a login for the application to use
CREATE LOGIN App1 WITH PASSWORD = ‘Pa$$w0rd’
GO
— Create a user-defined server role to manage the application login
CREATE SERVER ROLE App1AccountAdmins
GO
— Grant the server role the required permissions
GRANT CONTROL ON Login::App1 TO App1AccountAdmins
GO
— Create a login for a delegated application administrator
CREATE LOGIN JoeBloggs WITH PASSWORD = ‘Pa$$w0rd’
GO
— Add the delegated application admin to the server role
ALTER SERVER ROLE App1AccountAdmins ADD MEMBER JoeBloggs
GO
— Now change the connection and log in as JoeBlogs
— Try to change the application login’s password
ALTER LOGIN App1
WITH Password = ‘T0pS3cret’;
/*
This results in the following error:
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login ‘App1’, because it does not exist or you do not have permission.
/*
Any ideas if this is a bug, by-design, or because I’ve brain-freezed and missed something obvious?