SQL: Apply permissions to all stored procedures or tables

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

DECLARE @SQL varchar(100)

DECLARE cObjects CURSOR FAST_FORWARD
FOR
    SELECT 'GRANT EXECUTE ON ' + name + ' TO [usrTest]' AS SQL
    FROM   sysobjects 
    WHERE  xtype in ('P')

/*
Or, to Grant select, insert, update, and/or delete permissions, use the following
select statement instead of the one above:
 
FOR SQL Server 2005:
 
SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE ON [' + S.name + '].[' + O.name 
         + '] TO [usrTest]' AS SQL
FROM       sys.sysobjects O
INNER JOIN sys.Schemas S
      ON   O.uid = S.schema_id
WHERE      xtype in ('U','V')
 
FOR SQL Server 2000:
 
SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE ON [' + S.name + '].[' + O.name 
         + '] TO [usrTest]' AS SQL
FROM       sysobjects O
INNER JOIN sysusers S
      ON   O.uid = S.UID
WHERE      xtype in ('U','V')
*/

OPEN cObjects
FETCH NEXT FROM cObjects INTO @SQL
 
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@SQL)
    FETCH NEXT FROM cObjects INTO @SQL
END
 
CLOSE cObjects
DEALLOCATE cObjects

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.