Script to create database role to execute stored procedures

Posted: December 16, 2008 in Sqlserver

— Script to create a database role for non db_owners to grant access to execute Stored procedures.

USE pubs
GO

EXEC sp_addrole 'db_executor'
GO

USE pubs

GO

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
GO


Thanks & Regards
Md.S.Hassan

Advertisements

Comments are closed.