Permissions Script

October 17, 2008 by Michelle Ufford
Filed under: T-SQL Scripts 

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.

Tags: ,

Comments

One Comment on Permissions Script

  1. Script to give permissions for all stored procs in a database « Sql Server Collection on Mon, 14th Sep 2009 3:32 am

    2. [...] 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.