Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant permission to create table in SP Expand / Collapse
Author
Message
Posted Wednesday, February 23, 2011 10:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 24, 2011 11:30 PM
Points: 17, Visits: 50
Hi everyone,
There is one user who has permission to execute one SP.
In that SP, one table is created in database and finally it is dropped at the end of the SP.
When this user is trying to run this SP he is getting following error.
"CREATE TABLE permission denied in database".

Please help me wth this.
Post #1068668
Posted Thursday, February 24, 2011 3:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:18 PM
Points: 843, Visits: 2,559
Is he member of the dbcreator server role?

updated 2/25/2011
damn, for some reason I read CREATE DATABASE


__________________________
Allzu viel ist ungesund...
Post #1068761
Posted Thursday, February 24, 2011 3:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 9,928, Visits: 11,196
atikdesai (2/23/2011)
Hi everyone,
There is one user who has permission to execute one SP.
In that SP, one table is created in database and finally it is dropped at the end of the SP.
When this user is trying to run this SP he is getting following error.
"CREATE TABLE permission denied in database".

Please help me wth this.

You can sign the procedure with a certificate or key to grant CREATE TABLE within the procedure only, but why are you creating and dropping a 'real' table inside a procedure?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1068765
Posted Thursday, February 24, 2011 11:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 24, 2011 11:30 PM
Points: 17, Visits: 50
SQLkiwi (2/24/2011)
atikdesai (2/23/2011)
Hi everyone,
There is one user who has permission to execute one SP.
In that SP, one table is created in database and finally it is dropped at the end of the SP.
When this user is trying to run this SP he is getting following error.
"CREATE TABLE permission denied in database".

Please help me wth this.

You can sign the procedure with a certificate or key to grant CREATE TABLE within the procedure only, but why are you creating and dropping a 'real' table inside a procedure?


Thanks for replying... Actually this SP has been developed by some other person and he left.
Please tell me how I can sign the procedure with a certificate or key to grant CREATE TABLE within the procedure only.
Post #1069410
Posted Thursday, February 24, 2011 11:53 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 9,928, Visits: 11,196
atikdesai (2/24/2011)
Actually this SP has been developed by some other person and he left.

Well that sort of thing happens from time to time
You should still be able to understand what it is doing, and why it needs to create a real table. It seems unwise to modify something you don't understand yet.

Please tell me how I can sign the procedure with a certificate or key to grant CREATE TABLE within the procedure only.

There is a Microsoft tutorial here:
http://msdn.microsoft.com/en-us/library/bb283630.aspx

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1069414
Posted Friday, February 25, 2011 12:48 PM


Default port

Default portDefault portDefault portDefault portDefault portDefault portDefault portDefault port

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:51 PM
Points: 1,433, Visits: 3,230
I would suggest that you create a temp table (in tempdb) for this as workaround.

You will still have the create table permission issue in tempdb but what have done is maintain an empty 'template' table in the current database, then create the temp table in tempdb via select into.

If you tempdb is tuned for good performance you usually be better off with your temp tables in there Vs in the current database.




The probability of survival is inversely proportional to the angle of arrival.
Post #1069818
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse