Creating a script to grant execute to stored procedures where none exist It’s very common to forget to script out permissions for a stored procedure when generating the script. There’s nothing more frustrating than deploying some code changes, only to have them not work because you have elevated security on your development SQL Server, but not on QA or Production. If you find one, likely you’ll find it several places. How do you quickly locate the procedures which don’t have the desired permission set? Better yet, how do you fix it quickly without writing a lot of script or manually setting the permissions in SQL Server Management Studio?
The script below is one I pulled off the internet (http://sqlfool.com/story/46/date-posted/06-06-0939/) and modified a little. Since the environment I am in now uses multiple schemas, I added the ability to specify schema to search for.
Here’s the script:
DECLARE @role VARCHAR(50) DECLARE @targetSchema VARCHAR(50)SET @targetSchema = 'dbo'SET @role = 'MyUser'SELECT 'GRANT EXECUTE ON ' + SPECIFIC_SCHEMA + '.[' + SPECIFIC_NAME + '] TO ' + @role + CHAR(10) + 'GO'FROM INFORMATION_SCHEMA.ROUTINES WITH (NoLock)WHERE SPECIFIC_NAME Not In (SELECT o.name FROM sys.database_permissions p WITH (NoLock) INNER Join sys.objects o WITH (NoLock) ON p.major_id = o.OBJECT_ID INNER Join sys.database_principals u WITH (NoLock) ON u.principal_id = p.grantee_principal_id WHERE u.name = @role)AND ROUTINE_TYPE = 'PROCEDURE'AND SPECIFIC_NAME not like 'sp_%'AND SPECIFIC_SCHEMA = @targetSchemaORDER BY SPECIFIC_NAME
Just change the @targetSchema and @role variables and run it. The results will be a list of procedures on the current database which don’t have execute permissions assigned to the indicated @role. You can then copy those procs you want to grant execute on into the query window and execute them.
Powered by: newtelligence dasBlog 2.3.9074.18820
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2012, © Copyright 2010
E-mail