Tuesday, November 26, 2013

Grant Execute to all stored procs in a database

This script gives execute rights to all stored procs for the [USERNAME].  It uses a cursor, but it works well

DECLARE procs CURSOR FOR SELECT [name]
                         FROM sys.objects
                         WHERE type IN('p','AF','FN')
DECLARE @name AS VARCHAR(250)
DECLARE @stmt AS VARCHAR(1000)

OPEN procs

FETCH NEXT FROM procs INTO @name

WHILE @@FETCH_STATUS=0
BEGIN
    SET @stmt='GRANT EXECUTE ON '+@name+' TO [USERNAME]'
  
    EXEC(@stmt)
  
    PRINT @stmt
  
    FETCH NEXT FROM procs INTO @name
END

CLOSE procs

DEALLOCATE procs

No comments:

Post a Comment