Restoring database from another server and assigning permissions
-
I have a database coming from another SQL Server over which I have no view or control (it's on another domain). The database is a source system for my data warehouse. I have an application called WhereScape RED which is a data warehouse environment. RED has its own scheduler. The RED scheduler has its own domain user.
I can setup a job in the RED scheduler which:
1. Can check for the existence of a changed database backup at a remote location
2. If that succeeds, it'll overwrite the backup in a local store
3. If that succeeds, it'll verify the database backup using RESTORE WITH VERIFY_ONLY
4. If that succeeds, it'll restore the database to the local SQL Server
5. If that succeeds, it'll continue with the data warehouse load
The RED job runs from within another database (my data warehouse). This works fine on my development box where the RED scheduler domain user has sysadmin access. However, I need to be a bit more restrictive to get my project up to the production environment. So, my requirements of the RED scheduler user (relative to SQL Server) are to be able to:
1. Restore the database to the local SQL Server
2. Assign read permissions on the database to itself (i.e. add the RED scheduler domain user to the db_datareader database role in my restored source database)
I've revoked the sysadmin access and am trying to work out what permissions I need. I've added the dbcreator server role which allow the scheduler to restore the database. I've added the RED scheduler to the securityadmin server role hoping I'd be able to add permissions for the source database to let the RED scheduler access it.
The RED scheduler user does not exist in the restored database (obviously, since it's from another domain). So, before I can add the RED scheduler to the db_datareader role, I need to add the RED scheduler login to the restored source database. For this, I'm trying to use the "CREATE USER ... FOR LOGIN ..." command. This command only works against the local database, which means I need to USE that database first (I've achieved this by creating a string in a stored procedure then executing that string using sp_executesql). However, when I try and use the source database SQL baulks and tells me RED scheduler user doesn't exist in that database. I know that, which is why I'm trying to add it!
So, how can I achieve what I'm trying to do? My restrictions are:
- The script has to be called from within a Windows host script (which could be through OSQL) or from within a stored procedure
- The script will be executed relative to my data warehouse database
- The script has to run from inside my RED job to let the job determine the work flow and also to log any errors from within one spot (realising that I could overcome the issue if I executed something outside the RED scheduler and through SQL Server Agent or similar instead - I won't have the logging and work flow abilities as readily available)
- I need to limit permissions assignment to the RED scheduler user as much as possible
- The process *must* be scripted as I can't expect a DBA to keep re-assigning permissions
- I have no control over the source database
Any ideas?
Sam
-
Geez sam, my head is spinning from that MS exam question of yours.
Tell you what... let's chop this up into smaller pieces and tackle it that way.
Ask me one question and I'll try to help you with that specific issue. Then we can move on to the others. How does that sound?
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
I thought that a security admin would be able to add a user to any database. Is that not the case? Have you tried logging in as the RED user and running the SQL?
If this is 2005, you could do an EXECUTE AS, with a sysadmin or similar permissioned user to to the add user.
-
OK, I'll try and boil the question down...
"How can I restore a database to a different server using a login which doesn't exist in the original database; AND add the same login to the restored database, executed through that login? Via script."
Make any more sense?
-
EXECUTE AS for the add login step.
Otherwise do what you are doing.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply