February 19, 2011 at 12:55 pm
I have been asked to write a single stored procedure which restores a db, then drops some users and creates new analogous users as a precaution, then does some more stuff. The sp cannot live in the db I'm going to restore, so I was going to create it in master.
But... is there a way to drop/create database users in a database other than the current one? I cannot use the USE statement from within a sp after the restore to switch database context.
CREATE PROCEDURE BlahBlah --sp in master db
AS
BEGIN
RESTORE DATABASE [desired DB]............
USE [desired DB]; --can't do this
CREATE USER xyz FOR LOGIN xyz;
END
February 19, 2011 at 11:17 pm
AFAIK no.
- what's the need for this to be in a sproc ?
- Can't you use a sqlagent job or a sqlcmd to perform the restore and other needed actions for you ?
- As a last option, you could have your sproc generate dynamic sql and sp_executesql that from within the sproc itself.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2011 at 9:57 am
The desire for a single sp is so that a non-sql dev group can run it easily and repeatedly- they need to compare the resulting data when run thru different systems. So the group needs to set the data in a known state and process it with the existing system, then set the data back to the exact same state and run it through a new system and compare the two resulting data sets.
We need to use a backup from production, and because of privacy laws we cannot bring this data back to different domain servers. So we need to be extremely careful to change db users that the apps use to hit the db, change guids, etc to hopefully eliminate the possibility of this accidentally being run against the prod servers.
February 20, 2011 at 10:06 am
I would prepare a job for that and link that one to an alert.
All your test users have to do is raise the error and the restore job will get started.
This way you can pull some info regarding when and how many times they want to restore your db and you as a dba/sysadmin have control over the job as a whole unit of processing.
Have a look at:"help to tighten use of cmdshell or sp_start_job" http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply