Post reply

Grant Execute Permissions in a script

  • August 24, 2004 at 1:53 pm

    #162453

    I have the following script that I'm trying to use to grant execute permissions (to user named intranet) on all my stored procedures.  This is giving me an error on the GRANT line:

    Server: Msg 170, Level 15, State 1, Line 16

    Line 16: Incorrect syntax near '@storedprocname'.

    Here's the script:

    DECLARE @storedprocname varchar(50)

    DECLARE storedprocs_cursor CURSOR FOR

    SELECT name FROM Sysobjects WHERE Xtype = 'P' AND SUBSTRING(name,1,2) <> 'dt'

    OPEN storedprocs_cursor

    -- Perform the first fetch.

    FETCH NEXT FROM storedprocs_cursor INTO @storedprocname

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

       GRANT Execute ON @storedprocname TO intranet

       -- This is executed as long as the previous fetch succeeds.

       FETCH NEXT FROM storedprocs_cursor INTO @storedprocname

    END

    CLOSE storedprocs_cursor

    DEALLOCATE storedprocs_cursor

    GO

    Any help would be most appreciated.  Thanks.

  • August 24, 2004 at 2:30 pm

    #520414

    I think it is complaining about the table name being a variable. I've always used dynamic SQL with this sort of thing.

    exec( 'GRANT EXECUTE ON ' + @storedprocname + ' TO intranet' )

     

  • August 24, 2004 at 2:51 pm

    #520418

    That did the trick.  Thank you so much.  You are my hero.

  • August 24, 2004 at 3:33 pm

    #520427

    Sweet! I'm a hero!

    ::flexes::

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply