| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2005-03-22 : 05:01:21
|
| I want to give a user DBO access to tempdb..I am using the following command...use tempdbgosp_addrolemember @rolename = 'db_owner' , @membername = 'user_name' But everytime i restart the DB..the access is removed (since the tempDb gets created for every restart..)i tired to put the above commands in sp_procoption....but that is also not working..(sp_procoption can be used to execute the commands at db restart..)The above command can not be put in a sp...How to achieve this?------------------------I think, therefore I am |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-03-22 : 07:34:40
|
| "I want to give a user DBO access to tempdb.."answering the question "WHY?"....might get you an answer faster |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-22 : 07:41:17
|
| Add them to db_owner in model, that will add them to tempdb upon restart. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2005-03-22 : 08:01:29
|
| But that will make the user, DBO for all the new DBs right?------------------------I think, therefore I am |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2005-03-22 : 08:09:27
|
quote:
Originally posted by AndrewMurphy
"I want to give a user DBO access to tempdb.."answering the question "WHY?"....might get you an answer faster
I want to BULK Insert the data into a table.For BULK INSERT data the user should be DBO..I can not give the DBO access to my application user, Hence i am doing it in a temp DB..------------------------I think, therefore I am |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-22 : 08:09:56
|
| Well, yeah. No big deal to go into a new DB and drop them. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2005-03-22 : 08:15:10
|
| TRUE..But slightly concerned ....if i forget about the same in future..!!!!My app user is also a DBO ....can you imagine that...i will better give a revolver to my user..(kidding;-))Thanks anyway for the solution. Its a nice option..------------------------I think, therefore I am |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-03-22 : 09:03:34
|
| You could also have a startup procedure or job that runs and gives them dbo access on tempdb only. :) That would probably be a preferred option in this case. Have you looked at Bulk Administrator?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2005-03-22 : 09:09:05
|
quote:
Originally posted by derrickleggett
You could also have a startup procedure .....
I tried that...we can use sp_procoption....for startup commands...The input parameter to sp_procoption is again a sp. So all the startup commands need to be put into a user sp. But we can not give sp_addrolemember/use db_name in a SP......------------------------I think, therefore I am |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-03-22 : 10:15:52
|
| You can setup a job. Set the job schedule to run when SQL Server Agent starts. In the job step, just specify the database you want to use as tempdb.If you want to script this out, you would use sp_add_jobschedule with @freq_type = 64MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2005-03-22 : 10:45:07
|
| Tried this..its working!!I have set the job schedule to run when SQL Server Agent starts....but there is a possibility that sql agent may not have re-started because of DB restart.I may have to check in my sql code for that....Thanks for the info...------------------------I think, therefore I am |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-22 : 16:00:57
|
| We have a NET START scheduled task (DOS "at" sheduler) that restarts SQL Agent (if everything else has failed to restart it!!) which runs every hourKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-22 : 16:03:09
|
| Why do you need to use tempdb for this? Why not create a new database just for this process?Tara |
|
|
|
|
tempdb dbo