are you logged in as the same user in both cases? I ran a little test logged in as admin and it works. Logged in as Joe User it raises an error (GRANTOR does not have permission to GRANT permission)
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
-- this is the procedure that i want to grant execute permissions to joeuser
create procedure blah
as
select current_timestamp
GO
create procedure grantperm
as
grant execute on blah to joeuser
GO
grant execute on grantperm to joeuser
GO
So, JoeUser logs in via QA, and execs grantperm, which failed:
Server: Msg 4613, Level 16, State 1, Procedure grantperm, Line 4
Grantor does not have GRANT permission.
Then Admin logged in via QA and execs grantperm:
The command(s) completed successfully.
ahhh hold on - I just raised complexity by using dynamic sql like you and now I think I see same problem. The SP appears to execute but permission is not, in fact, granted.
You can't grant permiissions to run the procedure you are creating until after it has been created, so do it separately.
Also, CREATE PROCEDURE must be run as a batch by itself, you will have to run a command to create the procedure, then run another command to grant permissions to access it.
iptrader's procedure is not granting permission to itself.
The procedure is called Build_Test
Inside the procedure, permission is being granted to table "[@Client]_Test" and procedure "Build_Car"
When I tried this out, I was able to get the non-dynamic-sql grants to work. However the dynamic sql grants did not work (any errors seem to have been consumed because the test sp returned no errors and appeared to have worked - only problem being that permission was not, in fact, granted).
oooh wait a minute - maybe I'm confused by a typo:
IPTrader, in the beginning of your post, you call the procedure "Build_Test"
Near the bottom, you seem to be referring to the same procedure as "Build_Car"
If that is the case, crescendo is correct - you can't grant the permission to a procedure before it is created.
0
iptraderAuthor Commented:
Sorry, name of the procedure is Build_Test. Build_Car is something completely different. My apologies for the confusion. It has been a really long day :)
I'm confused. When you say, "I need to run the GRANT statements inside the procedure", do you mean the statements granting access to the newly-created table, or the statements granting access to the stored procedure?
If it's the latter, you have to do it separately, after you have created the procedure.
What error messages are you getting?
0
iptraderAuthor Commented:
Thank you all for the help. Crescendo is right. It was a matter of where I placed the code.
Best,
IPT
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Try logging in as an admin and see if it works (i suspect it would).
Why not just deploy the SP and then manually grant permissions to execute it?