July 12, 2005 at 6:09 pm
When setting up log shipping, you quite often have database level users out of sync with server level logins ( generally because the sql server logins are missing on the standby server). I've searched many articles on this and nearly all offer some convoluted approach to copying logins from the primary log shipping server.
If you can get the SID from sysusers in the restored database on the standby server, what is the downside to creating the login as below, where you specify the desired sid to match sysusers. Seems so simple there must be a hitch. I'm only talking about situations with very few sql server logins.
EXEC sp_addlogin 'Michael', 'chocolate', 'pubs', 'us_english', 0x0123456789ABCDEF0123456789ABCDEF
Randy
July 12, 2005 at 6:44 pm
There is no hitch, that will work perfectly. (Assuming a login of the same name doesn't already exist.) Creating them correctly in the first place is so much easier than fixing them later that it doesn't get written up very much.
July 12, 2005 at 8:21 pm
As Scott has indicated, there isn't a hitch. sp_addlogin was built with the functionality to specify the SID for cases like you've mentioned. In fact, you can get the password and pass it using sp_addlogin without re-encrypting it meaning that doesn't pass in plaintext. It wouldn't be unheard of for a disaster recovery solution to script out the logins with SIDs and passwords (encrypted) to quickly re-create a master database in case a SQL Server goes down.
Also, it goes without saying, but Windows logins obviously avoid this issue since SIDs are dependent on the domain SID. Therefore, as long as the domain comes back like it should (admins are restoring not creating new), this scenario gets avoided even if you have to execute a bunch of sp_grantlogin queries.
K. Brian Kelley
@kbriankelley
July 13, 2005 at 10:26 pm
Please note that a script that synchronizes your server logins is available on SQL Farms' SQL Education Center at
http://education.sqlfarms.com/ShowPost.aspx?PostID=80
It's free...
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply