Dear Brian:

I really am a fan of yours after going thru this..

In my case, I took it a step further by adding the Table functions and the regular functions... I thank you very much for your contribution...I am a happy DBA!

Enclosed is the code for a stored proc which incorporates the granting of permissions to stored procs, table functions and regular functions. By creating this procedure in master database, I can call it from any user database to maintain permissions for the db_executor role.

MELVYN

Create proc sp_grantpermsprocsfunctions

as

/**  Procedure :   sp_grantpermsprocsfunctions

**                    

**  File      :    

**    

**  Author: MELVYN PATRICK LOPEZ     

**  Date:  03/08/2006

**

**  Description : This Procedure is to be used to maintain db_executor role

**                    Inspired by Brian Kelly at SQL SERVER CENTRAL

**                   

**                      

**               

**  Parameters : NONE

**

**  Return Values :      

**   

**  Data Retrieval Values: 

** 

**  Errors: 

**

**  Tables

**     Select : cursor

**                    

**       

**     Insert :

**       

**     Update :

**                     

**     Delete : 

**        

**  Procedures :

**     Calls :   sp_executesql 

**       

**     Called by : 

**     

**  Modified:

**

**

*/ 

BEGIN

DECLARE @SQL nvarchar(4000),

  @Owner sysname,

  @StoredProcedure sysname,

  @Return int

-- Cursor of all the stored procedures in the current database

DECLARE cursStoredProcedures CURSOR FAST_FORWARD

FOR

SELECT USER_NAME(uid) Owner, [name] StoredProcedure

FROM sysobjects

WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursStoredProcedures

INTO @Owner, @StoredProcedure

-- Set the return code to 0

SET @Return = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))

  BEGIN

    -- Create the SQL Statement. Since we're giving

    -- access to all stored procedures, we have to

    -- use a two-part naming convention to get the owner.

    SET @SQL = 'GRANT EXECUTE ON [' + @Owner

          + '].[' + @StoredProcedure

          + '] TO db_executor'

    -- Execute the SQL statement

    EXEC @Return = sp_executesql @SQL

    -- Get the next row

    FETCH NEXT FROM cursStoredProcedures

    INTO @Owner, @StoredProcedure

  END

-- Clean-up after the cursor

CLOSE cursStoredProcedures

DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.

IF (@Return = 0)

  BEGIN

    -- Exited fine, commit the permissions

    COMMIT TRAN

  END

ELSE

  BEGIN

    -- Exited with an error, rollback any changes

    ROLLBACK TRAN

    -- Report the error

    SET @SQL = 'Error granting permission to ['

    + @Owner + '].[' + @StoredProcedure + ']'

    RAISERROR(@SQL, 16, 1)

  END

----------------------------------------------------------------------------

DECLARE cursTableFunctions CURSOR FAST_FORWARD

FOR

  SELECT USER_NAME(uid) Owner, [name] TableFunction

   FROM sysobjects

   WHERE xtype = 'TF'

OPEN cursTableFunctions

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursTableFunctions

INTO @Owner, @StoredProcedure

-- Set the return code to 0

SET @Return = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))

  BEGIN

    -- Create the SQL Statement. Since we're giving

    -- access to all stored procedures, we have to

    -- use a two-part naming convention to get the owner.

    SET @SQL = 'GRANT SELECT ON [' + @Owner

          + '].[' + @StoredProcedure

          + '] TO db_executor'

    -- Execute the SQL statement

    EXEC @Return = sp_executesql @SQL

    -- Get the next row

    FETCH NEXT FROM cursTableFunctions

    INTO @Owner, @StoredProcedure

  END

-- Clean-up after the cursor

CLOSE cursTableFunctions

DEALLOCATE cursTableFunctions

-- Check to see if the WHILE loop exited with an error.

IF (@Return = 0)

  BEGIN

    -- Exited fine, commit the permissions

    COMMIT TRAN

  END

ELSE

  BEGIN

    -- Exited with an error, rollback any changes

    ROLLBACK TRAN

    -- Report the error

    SET @SQL = 'Error granting permission to ['

    + @Owner + '].[' + @StoredProcedure + ']'

    RAISERROR(@SQL, 16, 1)

  END

-------------------------------------

DECLARE cursFunctions CURSOR FAST_FORWARD

FOR

  SELECT USER_NAME(uid) Owner, [name] fFunction

   FROM sysobjects

   WHERE xtype = 'FN'

OPEN cursFunctions

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursFunctions

INTO @Owner, @StoredProcedure

-- Set the return code to 0

SET @Return = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))

  BEGIN

    -- Create the SQL Statement. Since we're giving

    -- access to all stored procedures, we have to

    -- use a two-part naming convention to get the owner.

    SET @SQL = 'GRANT EXECUTE ON [' + @Owner

          + '].[' + @StoredProcedure

          + '] TO db_executor'

    -- Execute the SQL statement

    EXEC @Return = sp_executesql @SQL

    -- Get the next row

    FETCH NEXT FROM cursFunctions

    INTO @Owner, @StoredProcedure

  END

-- Clean-up after the cursor

CLOSE cursFunctions

DEALLOCATE cursFunctions

-- Check to see if the WHILE loop exited with an error.

IF (@Return = 0)

  BEGIN

    -- Exited fine, commit the permissions

    COMMIT TRAN

  END

ELSE

  BEGIN

    -- Exited with an error, rollback any changes

    ROLLBACK TRAN

    -- Report the error

    SET @SQL = 'Error granting permission to ['

    + @Owner + '].[' + @StoredProcedure + ']'

    RAISERROR(@SQL, 16, 1)

  END

END

GO