Permissions Script
Let’s get this blog thing started!
I sometimes find that appropriate permissions are not always assigned to stored procedures during creation. This usually occurs when a proc is scripted and re-deployed in a hurry, or when a non-DBA decides to play in my sandbox. I use this rather simple script to quickly find and grant all missing permissions.
DECLARE @schema_owner VARCHAR(20); SET @schema_owner = 'dbo'; SELECT [name] AS 'storedProcedure', 'Grant Execute On ' + @schema_owner + '.' + [name] + ' To [insertDatabaseRoleHere];' AS 'sqlCode' FROM sys.objects WITH (NoLock) WHERE [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 = '[insertDatabaseRoleHere]' ) And [type] = 'P';
This is especially useful for when you have a common database role that all stored procedures need to belong to; for example, infrastructures that share role membership to allow applications to execute procs.
Comments
One Comment on Permissions Script
-
Script to give permissions for all stored procs in a database « Sql Server Collection on
Mon, 14th Sep 2009 3:32 am
[...] http://sqlfool.com/2008/10/permissions-script/ Posted in scripts. Tags: resources, scripts. Leave a Comment » [...]
Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog. ![]()





