October 28, 2008 at 2:08 pm
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
October 28, 2008 at 2:27 pm
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.
October 28, 2008 at 2:39 pm
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
October 28, 2008 at 2:53 pm
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