Automation: Granting Read Perms for Developers

on August 16, 2010

Yeah, you heard me.

“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

Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.

How I Rigged this Up

The Script

Search

Posts or Comics for Your Inbox?