Multiple GRANT statements

  • Does anyone know if there is a limit to the number of GRANT statements allowed in a batch? I am attempting to run 28 GRANT EXECUTE statements for various stored procedures at one time. I get the "Command(s) completed successfully", however, the permissions are not being set on the objects. If I run the statements individually, or in smaller groups, the permissions set correctly. I did some searching to see if this is a known problem, but could not find anything. Any help is appreciated.

    SQL Server 2000 Dev Ed, SP3

    Windows 2003 Std Ed, SP2

  • After looking at this further, if I remove owner reference for the stored procedures, I am able to execute all 28 lines at once:

    Does NOT work:

    GRANT EXECUTE ON dbo.storedproc1 TO dbrole


    GRANT EXECUTE ON dbo.storedproc28 TO dbrole

    Does work:

    GRANT EXECUTE ON storedproc1 TO dbrole


    GRANT EXECUTE ON storedproc28 TO dbrole

    I would have thought having owner reference is better, but apparently not.

  • Are you sure those objects exist in the 'dbo' schema? And, are you sure you have permissions to grant access to those access (requires WITH GRANT privilege).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Yes to both. I'm guessing an error would have been thrown if either of these is false.

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

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