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.
SET nocount ON ; DECLARE @login_name SYSNAME = 'WAGGERTAIL\IWantToRead'; DECLARE @perms NVARCHAR(MAX) DECLARE @debug bit DECLARE @error_message NVARCHAR(4000); SET @debug=0 /********************* * Master perms *********************/ USE [master] ; --Create Login if needed IF ( SELECT COUNT(*) FROM sys.server_principals WHERE name = @login_name ) = 0 BEGIN PRINT '--Creating Login ' + QUOTENAME(@login_name) + ' on ' + @@SERVERNAME SET @perms = 'CREATE LOGIN ' + QUOTENAME(@login_name) + ' FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] ;' IF @debug=1 PRINT @PERMS ELSE begin try exec sp_executesql @perms ; end try begin catch SELECT @error_message = ERROR_MESSAGE(); RAISERROR (@error_message, 16, 2 ); end catch END ELSE PRINT '--Login already exists on ' + @@SERVERNAME --If the login still doesn't exist, just return, something's wrong... IF ( SELECT COUNT(*) FROM sys.server_principals WHERE name = @login_name ) = 0 BEGIN SELECT @error_message = 'We didn''t create our login... what''s going wrong??' RAISERROR (@error_message, 16, 2 ); RETURN; END --Grant view server state if needed IF ( SELECT COUNT(*) FROM sys.server_principals users JOIN sys.server_permissions prm ON users.principal_id = prm.grantee_principal_id WHERE users.name = @login_name AND permission_name = 'VIEW SERVER STATE' ) = 0 BEGIN PRINT '--Granting Server State on ' + @@SERVERNAME + ' TO ' + QUOTENAME(@login_name); SET @perms = 'GRANT VIEW SERVER STATE TO ' + QUOTENAME(@login_name); IF @debug=1 PRINT @PERMS ELSE begin try exec sp_executesql @perms ; end try begin catch SELECT @error_message = ERROR_MESSAGE(); RAISERROR (@error_message, 16, 2 ); end catch END ELSE PRINT '--View Server State already granted on ' + @@SERVERNAME /********************* * MSDB perms *********************/ USE [msdb] ; -- Create user if needed IF ( SELECT COUNT(*) FROM sys.database_principals users WHERE users.name = @login_name AND users.type = 'G' -- Windows Group ) = 0 BEGIN PRINT CHAR(10) + '--Working on msdb...' PRINT '--Creating user...' SET @perms = 'CREATE USER ' + QUOTENAME(@login_name) + ' FOR LOGIN ' + QUOTENAME(@login_name); IF @debug=1 PRINT @PERMS ELSE begin try exec sp_executesql @perms ; end try begin catch SELECT @error_message = ERROR_MESSAGE(); RAISERROR (@error_message, 16, 2 ); end catch END ELSE PRINT '--User ' + QUOTENAME(@login_name) + ' already created in MSDB' IF ( SELECT COUNT(*) FROM sys.database_principals dbrole JOIN sys.database_role_members rel ON rel.role_principal_id = dbrole.principal_id JOIN sys.database_principals mem ON rel.member_principal_id = mem.principal_id AND mem.name = @login_name WHERE dbrole.name = 'db_datareader' ) = 0 BEGIN PRINT '--Granting datareader...' IF @debug=1 PRINT 'EXEC sp_addrolemember N''db_datareader'',' + @login_name ELSE EXEC sp_addrolemember N'db_datareader', @login_name END ELSE PRINT '--Datareader for ' + @login_name + ' already granted in MSDB' IF ( SELECT COUNT(*) FROM sys.database_principals dbrole JOIN sys.database_role_members rel ON rel.role_principal_id = dbrole.principal_id JOIN sys.database_principals mem ON rel.member_principal_id = mem.principal_id AND mem.name = @login_name WHERE dbrole.name = 'SQLAgentReaderRole' ) = 0 BEGIN PRINT '--Granting SQLAgentReaderRole...' IF @debug=1 PRINT 'EXEC sp_addrolemember N''SQLAgentReaderRole'',' + @login_name ELSE EXEC sp_addrolemember N'SQLAgentReaderRole', @login_name END ELSE PRINT '--SQLAgentReaderRole for ' + @login_name + ' already granted in MSDB' /****************************************** * Loop through user dbs and set perms... ******************************************/ DECLARE @dbs TABLE ( dbname SYSNAME ) DECLARE @dbname SYSNAME ; INSERT @dbs SELECT name FROM sys.DATABASES WHERE database_id > 4 WHILE ( SELECT COUNT(*) FROM @dbs ) > 0 BEGIN SELECT TOP 1 @dbname = dbname FROM @dbs PRINT CHAR(10) + '--Working on ' + QUOTENAME(@dbName) + '...' SELECT @perms = ' use ' + QUOTENAME(@dbName) + ' if (select count(*) from sys.database_principals users where users.name=' + QUOTENAME(@login_name,'''') + ' and users.type=''G'' -- Windows Group ) = 0 BEGIN print ''--Creating user ''' + QUOTENAME(@login_name,'''') + ''' on '' + DB_NAME() CREATE USER ' + QUOTENAME(@login_name) + ' FOR LOGIN ' + QUOTENAME(@login_name) + ' END ELSE print ''--User ' + QUOTENAME(@login_name) + ' already created in '' + @@SERVERNAME + ''.'' + DB_NAME() ' IF @debug=1 PRINT @PERMS else begin try exec sp_executesql @perms ; end try begin catch SELECT @error_message = ERROR_MESSAGE(); RAISERROR (@error_message, 16, 2 ); end catch SELECT @perms = ' use ' + QUOTENAME(@dbName) + ' if (select count(*) from sys.database_principals dbrole join sys.database_role_members rel on rel.role_principal_id=dbrole.principal_id join sys.database_principals mem on rel.member_principal_id=mem.principal_id and mem.name=' + QUOTENAME(@login_name,'''') + ' where dbrole.name = ''db_datareader'' ) = 0 begin print ''--Granting db_datareader ''' + QUOTENAME(@login_name,'''') + ''' on '' + DB_NAME() exec sp_addrolemember @rolename=''db_datareader'', @membername=' + QUOTENAME(@login_name,'''') + ' END ELSE print ''--VIEW DEFINITION already granted to ''' + QUOTENAME(@login_name,'''') + ''' on '' + DB_NAME() ' IF @debug=1 PRINT @PERMS else begin try exec sp_executesql @perms ; end try begin catch SELECT @error_message = ERROR_MESSAGE(); RAISERROR (@error_message, 16, 2 ); end catch SELECT @perms = ' use ' + QUOTENAME(@dbName) + ' if (select count(*) from sys.database_principals users join sys.database_permissions prm on users.principal_id =prm.grantee_principal_id where users.name=' + QUOTENAME(@login_name,'''') + ' and permission_name=''VIEW DEFINITION'' ) = 0 begin print ''--Granting VIEW DEFINITION ''' + QUOTENAME(@login_name,'''') + ''' on '' + DB_NAME() GRANT VIEW DEFINITION TO ' + QUOTENAME(@login_name) + ' end ELSE print ''--VIEW DEFINITION already granted to ''' + QUOTENAME(@login_name,'''') + ''' on '' + DB_NAME() ' IF @debug=1 PRINT @PERMS else begin try exec sp_executesql @perms ; end try begin catch SELECT @error_message = ERROR_MESSAGE(); RAISERROR (@error_message, 16, 2 ); end catch SELECT @perms = ' use ' + QUOTENAME(@dbName) + ' if (select count(*) from sys.database_principals users join sys.database_permissions prm on users.principal_id =prm.grantee_principal_id where users.name=' + QUOTENAME(@login_name,'''') + ' and permission_name=''SHOWPLAN'' ) = 0 begin print ''--Granting SHOWPLAN ''' + QUOTENAME(@login_name,'''') + ''' on '' + DB_NAME() GRANT SHOWPLAN TO ' + QUOTENAME(@login_name) + ' end ELSE print ''--SHOWPLAN already granted to ''' + QUOTENAME(@login_name,'''') + ''' on '' + DB_NAME() ' IF @debug=1 PRINT @PERMS else begin try exec sp_executesql @perms ; end try begin catch SELECT @error_message = ERROR_MESSAGE(); RAISERROR (@error_message, 16, 2 ); end catch -- Move on to the next DB DELETE FROM @dbs WHERE dbname = @dbname END
