Grant Permissions on all your Stored Procedures

A SQL script for re-assigning the permissions for stored procedures.
created by on 2012-09-05
DECLARE @DB SYSNAME;

SET @DB = Db_name()

DECLARE @U SYSNAME;

SET @U = Quotename('domain\username')

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 ' + @NAME + ' TO ' + @U

            -- Run the SQL Statement you just generated
            PRINT @SQL
        END
  END

source: http://www.geekzilla.co.uk/view2356DC3A-9F53-4A95-9849-C4518B8E1F92.htm

Tags:
Fork allmark on GitHub