March 29, 2005 at 11:19 am
hi everyone,
We have a SP, which creates a table in the dbo schema:
select *
into dbo.tablename
from tableA
and then the SP tries to give select permissions to a sql user:
grant select on dbo.tablename to userA
When a regular user who has execute permission on the SP runs the SP, he gets:
Server: Msg 4613, Level 16, State 1, Procedure SP1, Line 4
Grantor does not have GRANT permission.
Does anyone have any suggestions on how to handle this? The user needs to have permission to execute this SP.
Thanks in advance.
March 29, 2005 at 11:53 am
A couple of comments: First off why don't you remove the table creation statement from the sp. Then the sp can populate the table (and truncate it when you are fininised if necessary) but then you don't have to worry about granting permissions within your sp. It's generally not a good idea to create sp's that contain DDL anyway as it can lead to excessive recompiles.
If you must leave the DDL in the sp, you would need to issue a GRANT SELECT...WITH GRANT statement, which assumes that you know which user will be executing the sp. The other option you have is to make the user in question a member of the db_securityadmin. I'm guessing that the user is part of the db_ddladmin group now which has the ability to create tables, but cannot issue a GRANT command.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 29, 2005 at 1:30 pm
In our environment we simply use
grant exec on <sproc> to [User/user group(we use groups)]
revoke exec on <sproc>to public
hope this helps.
Sheila Conlon (getting back)
March 29, 2005 at 2:08 pm
The problem is that granting execute on a stored procedure does not necessarily grant all the required permissions for all operations that the procedure might attempt to perform. This is easy to forget if all objects are owned by dbo. In the case of DML (INSERT, UPDATE, DELETE, and SELECT) SQL Server assumes that since dbo owns the base objects (tables) and the procedure, that there is an implied permission granted to anyone who has execute permission on the procedure (at least within the scope of the procedure.) The same does not apply to DDL (CREATE, DROP, ALTER, etc... nor to GRANT, REVOKE, and DENY statements.) These statements require explicit rights or group membership.
So granting execute rights on a proc that in turn performs a CREATE and GRANT operation is not sufficient. The user that executes the proc needs to be granted the ability to execute those commands independent of the execute rights he/she has on the proc.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 29, 2005 at 3:36 pm
Thank you very much for your help! We will re-write the SP and creat the table outside of the SP and grant the right permissions. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply