GRANT Permissions on all of your Stored Procedures using a query

Permissions are an essential security feature, and you'll often create Roles and Users when you copy your Database live. Rather than using the permissions dialog boxes in Enterprise Manager you can write some T-SQL to perform the GRANTS in Query Analyzer. This is relatively simple process.

Use dbo.sysobjects to get a list of your Stored Procedures

SELECT name
FROM dbo.sysobjects
WHERE type = 'P' AND category = 0

In the WHERE clause setting type to 'P' will only return the Stored Procedures from dbo.sysobjects. Of these records those with a category value of 0 are user stored procedures, you'll notice that I have added this to the WHERE clause.

Don't be tempted to use a CURSOR to get to all your stored procs

CURSORs utilise loads of resource, they're slow, and this only increases as table size increases. Try and use a simple loop to iterate rows in a table, I normally drive this using a MIN() on Unique field, selecting the next lowest value in the table, this is much faster than a cursor. See below.

DECLARE @ID    integer,
       @LAST_ID integer,

--Initialise the variable
SET @LAST_ID = 0

--Loop until we've returned all the records
WHILE @LAST_ID IS NOT NULL
BEGIN

    -- Get next lowest value
    SELECT @ID = MIN(id)
    FROM dbo.sysobjects
    WHERE id > @LAST_ID  AND type = 'P' AND category = 0
    
    SET @LAST_ID = @ID
END

Once you've got your loop working, you'll want to grab the details of the stored procedure and perform the GRANT. On each iteration of the loop you can build a SQL string then execute it using xp_execresultset

DECLARE @DB  sysname ; set @DB = DB_NAME()
DECLARE @U  sysname ; set @U = QUOTENAME('<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
        EXEC master.dbo.xp_execresultset @SQL, @DB

    END
    

END

And that's it. Loop round the dbo.sysobjects table, get each stored procedure in turn, build a SQL string and execute it.

Using a standard naming convention for your stored procs will allow you to extend this further by picking up specific groups of procedures in the initial query so you can GRANT functionality to the correct user or role.