Solved

Grant permissions to execute Stored Procedure

Posted on 2004-08-04
16
4,606 Views
Last Modified: 2008-01-09
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_AS NOT NULL ,
      [Column2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) 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


0
Comment
Question by:iptrader
  • 7
  • 5
  • 4
16 Comments
 
LVL 10

Expert Comment

by:ibost
ID: 11721447
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?
0
 

Author Comment

by:iptrader
ID: 11721475
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.
0
 
LVL 10

Expert Comment

by:ibost
ID: 11721503
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
 

Author Comment

by:iptrader
ID: 11721517
can U show me the code U ran?
0
 
LVL 10

Expert Comment

by:ibost
ID: 11721554
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.

0
 
LVL 10

Expert Comment

by:ibost
ID: 11721586
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.
0
 
LVL 9

Accepted Solution

by:
crescendo earned 300 total points
ID: 11721837
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.
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11721841
If you are doing it in T-SQL, simply stick a GO after the end of the SP create script, then do the GRANTs.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:ibost
ID: 11721863
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
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11721919
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.
0
 
LVL 10

Expert Comment

by:ibost
ID: 11721926
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?
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11721929
As it stands, the SP creates the table but doesn't grant access to it.
0
 
LVL 10

Expert Comment

by:ibost
ID: 11721993
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
 

Author Comment

by:iptrader
ID: 11722459
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 :)
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11724162
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?
0
 

Author Comment

by:iptrader
ID: 11728321
Thank you all for the help.  Crescendo is right.  It was a matter of where I placed the code.

Best,

IPT
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now