1  
   2  CREATE PROCEDURE spGrantExectoAllStoredProcs @user sysname
   3  AS 
   4  
   5  /*----------------------------------------------------------------------------
   6  -- Object Name: spGrantExectoAllStoredProcs
   7  -- Author: Edgewood Solutions
   8  -- Development Date: 03.19.2007
   9  -- Called By: TBD
  10  -- Description: Issue GRANT EXEC statement for all stored procedures 
  11  -- based on the user name that is passed in to this stored procedure
  12  -- Project: SQL Server Security
  13  -- Database: User defined databases 
  14  -- Business Process: SQL Server Security
  15  -- 
  16  ----------------------------------------------------------------------------
  17  -- Num | CRF ID | Date Modified | Developer | Description
  18  ----------------------------------------------------------------------------
  19  -- 001 | N\A      | 03.15.2007    | Edgewood | Original code for the GRANT 
  20  -- EXEC process
  21  --
  22  -- 
  23  */
  24  
  25  SET NOCOUNT ON
  26  
  27  -- 1 - Variable declarations
  28  DECLARE @CMD1 varchar(8000)
  29  DECLARE @MAXOID int
  30  DECLARE @OwnerName varchar(128)
  31  DECLARE @ObjectName varchar(128)
  32  
  33  -- 2 - Create temporary table
  34  CREATE TABLE #StoredProcedures
  35  (OID int IDENTITY (1,1),
  36  StoredProcOwner varchar(128) NOT NULL,
  37  StoredProcName varchar(128) NOT NULL)
  38  
  39  -- 3 - Populate temporary table
  40  INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
  41  SELECT ROUTINE_SCHEMA, ROUTINE_NAME
  42  FROM INFORMATION_SCHEMA.ROUTINES 
  43  WHERE ROUTINE_NAME NOT LIKE 'dt_%'
  44  AND ROUTINE_TYPE = 'PROCEDURE'
  45  
  46  -- 4 - Capture the @MAXOID value
  47  SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
  48  
  49  -- 5 - WHILE loop
  50  WHILE @MAXOID > 0
  51  BEGIN 
  52  
  53  -- 6 - Initialize the variables
  54  SELECT @OwnerName = StoredProcOwner,
  55  @ObjectName = StoredProcName
  56  FROM #StoredProcedures
  57  WHERE OID = @MAXOID
  58  
  59  -- 7 - Build the string
  60  SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user
  61  
  62  -- 8 - Execute the string
  63  -- SELECT @CMD1
  64  EXEC(@CMD1)
  65  
  66  -- 9 - Decrement @MAXOID
  67  SET @MAXOID = @MAXOID - 1
  68  END
  69  
  70  -- 10 - Drop the temporary table
  71  DROP TABLE #StoredProcedures
  72  
  73  SET NOCOUNT OFF
  74  GO