Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-05-12 : 04:56:47
|
| hello,I am trying to create a new user that only has permissions to run an sp, this sp drops tables then creates them,I have created the user then changed the permissions so the sp can execute and the user then has full permissions over the relevent tables. however when it run the sp I get an error saying table J_TEMP already exists.for some reason its not letting me drop it.how can I allow a user to do this without adding the user to the sysadmin group ?thanks for any help. |
|
|
MuadDBA
628 Posts |
Posted - 2004-05-12 : 11:17:16
|
| The user needs to own the specific table. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-12 : 12:49:57
|
| Why not use real temporary tables that anyone can drop?#J_TEMPTara |
|
|
|
tinks
Starting Member
34 Posts |
Posted - 2004-05-13 : 04:43:32
|
| you could try using transactions within the sproc ie.begin transaction /*drop your tables*/commit transaction begin transaction /*test to see if tables exist if not create them*/if not exists (select 1 from sysobjects where name = 'tablename')/*create tablename... */commit transaction Taryn-Vee@>-'-,--- |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-05-13 : 05:53:32
|
| tara thanks for the info, but if I use temp tables is the data stored in the tempdb ? if so I don't realy want to do that as the size of the tables are very large.taryn, so using transactions, this will allow any user with permissions to run the sp to drop tables ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-13 : 11:48:41
|
| Transactions has nothing to do with your problem. Yes temp tables are stored in tempdb. You'll need to grant db_owner role to the user in order to drop a dbo object. Or have the table created using the user's name, UserName.TableName.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-13 : 13:38:41
|
| You could also add whoever is going to use the proc to the db_ddladmin group. I don't know why in the world you would want to do this though. ??????????MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
dropping tables