Below is a script that I found that makes it very easy to set execute permissions to all all stored procs in a SQL Server 2005/2008 DB.

-- Set Username
DECLARE @U  sysname ; set @U = QUOTENAME('DB_USERNAME_HERE')
-- Set DB
DECLARE @DB  sysname ; set @DB = 'DB_NAME'
-- SET Schema
DECLARE @schema  sysname ; set @schema = 'SCHEMA_NAME'


DECLARE @ID           integer,
@LAST_ID     integer,
@NAME        varchar(1000),
@SQL         varchar(4000)

SET @LAST_ID = 0

WHILE @LAST_ID IS NOT NULL
BEGIN
SELECT @ID = MIN(id)
FROM dbo.sysobjects
WHERE id > @LAST_ID  AND type = 'P' AND category = 0

SET @LAST_ID = @ID

-- We have a record so go get the name
IF @ID IS NOT NULL
BEGIN
SELECT @NAME = name
FROM dbo.sysobjects
WHERE id = @ID

-- Build the DCL to do the GRANT
SET @SQL = 'GRANT EXECUTE ON ' +@DB + '.' + @schema +'.' + @NAME + ' TO ' + @U

PRINT 'setting execute permissions for: '+  @SQL
-- Run the SQL Statement you just generated
EXECUTE(@SQL)

END


END

Author

RBA Author

Leave a Reply