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!