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
REGISTER