SQL Fool Adventures in SQL Tuning – a blog for the rest of us

17Oct/080

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.