Yeah, you heard me.
Do you like to read?
“But Kendra, why would we want to grant developers read permissions? And why would we automate it? And at what point did you lose your mind?”
Well, Virginia, there may or may not be a Santa Claus, but there are a lot of developers and good reasons to give them read access on many SQL Server instances. In my world, it’s every instance in the pre-production environment, which is a couple hundred and growing. And it ain’t just read they’re needing, they should be empowered with all of:
- In master: View server state– so they can look at all those pesky spids they can block, and which may be causing problems they’re investigating.
- In msdb:
- db_datareader — so they can query things not easily seen through the GUI
- SQLAgentReaderRole — so they can look at currently executing jobs and history through the GUI
- In each user database:
- db_datareader — for troubleshooting, troublemaking, and general read-i-ness
- view definition — so they can see what they’re dealing with
- showplan — so there ain’t no excuse if they’re causing issues, and also to help further with the troubleshooting
And of course a user needs to be created in msdb and the user databases for this all to work out. Things to look out for: SQL Server will let you create a login and apply database level permissions with T-SQL without the user being created, but then you’ll find it doesn’t work.
When you’re dealing with a large pre-production environment and a large group of developers (> 75 total) who periodically change roles, this permission set can be a bit complicated to manage.
How I Rigged this Up
In my implementation, this script is in a job on an MSX master server, and our server build steps for pre-production servers include configuring the instance as a TSX subscriber and adding the job. The job is scheduled to run daily, so if new databases are created, restored, or dropped and recreated, permissions will automatically be re-applied. I like using the MSX job for the implementation because it’s easy for our DBA team to see the job on the instance and see exactly what it does, and if there’s any reason to exempt an instance then that’s very simple to do.
So with this solution, I don’t have to worry about the mechanics of applying read permissions. I just have to worry about making sure the right people are in the active directory group, and I can pass that task on to their managers.
Important: Because of how our active directory and firewalls are configured, I do not have to worry about this job being accidentally deployed to a production server and granting read where it should not. Something to think about! Don’t automate yourself into security violations. This was another reason I chose the relatively “transparent” solution of an MSX subscriber job rather than other solutions.
The Script
Disclaimer: This script was tossed together quickly. If you read the story above, you know I wasn’t even working on it for production. It supports windows groups only in its current form. (Please don’t use sql authentication to grant read. Domain groups are so much more auditable and controllable!) It grants permissions directly to a login and does not create a role in each database. This is for a good reason in my world, but the best practice is to create a role, so keep that in mind.
As always, test the stuffing out of this and edit as needed if you’d like to make use of it. Continue Reading →
Comments