Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
5 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Grant permissions to execute Stored Procedure
Posted on 2004-08-04
I have a stored procedure that looks somewhat like this:
CREATE PROCEDURE Build_Test (@Client varchar(30) = " ")
AS
begin
set nocount on
declare @sql varchar(8000)
set @sql = 'CREATE TABLE dbo.[' + @Client + '_Test] (' +
'[Column1] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_
[Column2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY] '
execute (@sql)
set nocount off
end
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [nsmith]'
Exec(@sql)
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [public]'
Exec(@sql)
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [ttoms]'
Exec(@sql)
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [ISTService]'
Exec(@sql)
GO
I also need to grant execute privileges to run the sp, since I will be calling it from an ASP page:
GRANT EXECUTE ON Build_Car TO [nsmith]
GRANT EXECUTE ON Build_Car TO [public]
GRANT EXECUTE ON Build_Car TO [ttoms]
GRANT EXECUTE ON Build_Car TO [ISTService]
I need to run the GRANT statements inside the procedure. However, they are not triggering when I run the sp. I had them placed right after this:
CREATE PROCEDURE Build_Car (@Client varchar(30) = " ")
AS
begin
Not working :). What to do?
Thanks,
IPT
CREATE PROCEDURE Build_Test (@Client varchar(30) = " ")
AS
begin
set nocount on
declare @sql varchar(8000)
set @sql = 'CREATE TABLE dbo.[' + @Client + '_Test] (' +
'[Column1] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_
[Column2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY] '
execute (@sql)
set nocount off
end
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [nsmith]'
Exec(@sql)
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [public]'
Exec(@sql)
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [ttoms]'
Exec(@sql)
Set @sql='GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [' + @Client + '_Test] TO [ISTService]'
Exec(@sql)
GO
I also need to grant execute privileges to run the sp, since I will be calling it from an ASP page:
GRANT EXECUTE ON Build_Car TO [nsmith]
GRANT EXECUTE ON Build_Car TO [public]
GRANT EXECUTE ON Build_Car TO [ttoms]
GRANT EXECUTE ON Build_Car TO [ISTService]
I need to run the GRANT statements inside the procedure. However, they are not triggering when I run the sp. I had them placed right after this:
CREATE PROCEDURE Build_Car (@Client varchar(30) = " ")
AS
begin
Not working :). What to do?
Thanks,
IPT
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
7-
5
-
4
16 Comments
I don't think you could do this unless the person calling the SP has permission to GRANT.
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?
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?
Well, the point is not to grant the permissions manually, since I want the user to execute the SP through ASP page.
I have permissions to GRANT. If I execute the GRANT statements independently, they work, just not in the SP.
I have permissions to GRANT. If I execute the GRANT statements independently, they work, just not in the SP.
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)
It was something like this:
-- 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.
-- 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.
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.
If you are doing it in T-SQL, simply stick a GO after the end of the SP create script, then do the GRANTs.
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).
-Ian
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).
-Ian
OK, but looking at the original code, either there needs to be a GO after the "end", or the "end" needs to come after the GRANTs.
ACK - nevermind. I had set up the dynamic sql SP wrong.
Having set it up correctly, it works for an admin. Still won't work for JoeUser (expected) because JoeUser does not have permission to GRANT.
IPTrader - does it give you any sort of error at all?
Having set it up correctly, it works for an admin. Still won't work for JoeUser (expected) because JoeUser does not have permission to GRANT.
IPTrader - does it give you any sort of error at all?
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.
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.
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 :)
iptrader:
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?
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?
Featured Post
Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses
Course of the Month5 days, 16 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.