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 (
    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 = '[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.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , . Bookmark the permalink.

One Response to Permissions Script

  1. Pingback: Script to give permissions for all stored procs in a database « Sql Server Collection

Leave a Reply

Your email address will not be published. Required fields are marked *