August 5, 2013 at 1:19 pm
Hello,
I have setup a log shipping for a couple of my databases (SQL 2008). I would like keep any additional logins created on the primary database in sync on the log shipping server. I would like to use the SSIS transfer logins task to accomplish this goal and run via a SQL Agent job. However, when I run the job I get the following error.
[Transfer Logins Task] Error: Execution failed with the following error: "Database 'DBNAME' cannot be opened. It is in the middle of a restore.".
I have the following settings on the task:
AllLoginsFromSelectedDatabases
If objects exist = Skip
CopySids = True
I understand the logins cant be added while the database is restoring. Is there anyway around this issue?
Thx
August 5, 2013 at 6:31 pm
Login is a server-scope object, not contained within a (user) database. It is stored in a master database.
A database user can be (but does not have to be) mapped to a login.
Query this views:
sys.database_principals
sys.server_principals
LS database is in "restoring" state, so it is not accessible for modifications (except by applying the logs) or reading (except by using database snapshots). Even if it was modifiable or readable, there is no use, because logins are NOT in the database and are not in the transaction log of that database, so they are not transferred via LS.
You could however create a sql login on the other server automatically, with the same SID as login on the primary server.
For windows (domain) logins, you do not have a SID problem, since AD stores that account with it's SID which is used on all servers in a domain.
You just need to create a login "from windows". See BOL CREATE LOGIN command.
After all the logins are transferred with the correct SID, db users are automatically linked to them. New db users are transferred via LS and linked automatically to logins, no action required from your side.
August 5, 2013 at 9:49 pm
Vedran,
This has set me on the correct path. Even though I can't map the login to the databases that are in the restoring state, I would like to have the logins created on the log shipping server with the correct SID. This should be simple enough to code and implement.
Thank you
August 7, 2013 at 10:28 am
The nice thing about this is that if you have created the logins with the same name and SID on the secondary server using something like sp_help_revlogin, then if you have to bring the secondary online, the DB users mapped to that login on the primary will automatically be associated on the secondary as well, as the association is based on SIDs.
I take advantage of that fact frequently for migrations using mirrors. It sounds cruel, but I hate orphaned users 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply