SQL Server Performance Forum – Threads Archive

Log Shipping SQL Login Transfers

I was reading up on how to transfer SQL logins for log shipping at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx. I also know you can create identical SQL logins (passwords and SIDs) on other servers using sp_help_revlogin, detailed herehttp://support.microsoft.com/default.aspx?scid=kb;en-us;246133. There is something comforting about having the same SIDs on both boxes, since you do not have to use the sp_change_users_login procedure to remap the database user back to the SQL Login on the standby server. Anyone use sp_help_revlogin instead of the MS recommended method? Also, since I am only shipping one of many DBs, I may not need all the logins on the server, just the ones that map to users in the DB. Anyone account for this? Thanks, Matt

Yes we have used that SP before and if you need specified logins then you need to edit the results and keep it accordingly. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi, I have implemented log shipping in sql 2k standard edition, i am not sure how to transfer login id’s and pwd’s from primary to standby server windows users are accessing the databases. any help would be appreciated

Deal that issue with this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;246133 link. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I was shipping one database (from a primary server with many databases) and decided to add some automation to the login transfer. Here are the steps I created in the DTS (maybe not the most efficient, but it works) I start off with general housekeeping. This will make sense later. 1. Delete the file \primaryc$loginscript.sql
2. Delete all rows from primary.LoginTransfer.dbo.Tbl_OrphanImport
3. Delete the LoginTransfer.dbo.Tbl_TransferNames table if it exists 4. Obtains the orphan names from the StandbyServer.ShippedDB database and stores them in Primary.LoginTransfer.dbo.Tbl_OrphanImport (Transform data task)
5. Using Execute SQL Tasks, pulls the names from Primary.master.dbo.syslogins where the name in Primary.DGX_LoginTransfer.dbo.Tbl_OrphanImport matches the name in Primary.ShippedDB.dbo.sysusers and the SID in Primary.ShippedDB.dbo.sysusers matches the SID in Primary.master.dbo.syslogins. It stores these names into Primary.LoginTransfer.dbo.Tbl_TransferNames.
6. Uses OSQL task on the Primary to run the stored procedure sp_help_revlogin. http://support.microsoft.com/default.aspx?scid=kb;en-us;246133). I have edited the stored procedure to only output the names specified in primary.LoginTransfer.dbo.Tbl_TransferNames â€“ only the orphaned users. The output of this OSQL command will be stored in \primaryc$loginscript.sql
7. Use OSQL to run \primaryc$loginscript.sql against StandbyServer to create the missing SQL logins.
8. — Repeats steps 1-3 to clean up — One big requirement is that the ShippedDB on the Standby server must allow access when it is not being updated (in order to figure out which orphans have been orphaned after being shipped). Also, make sure you are not pulling this information from the Standby WHILE the log shipping updates are in progress or you will be locked out and get errors. I scheduled this to run once a night as users are not frequently updated. I also included something to set the default DB login to the ShippedDB for any SQL Logins transferred to the standby.

All the defined steps seems to be perfect and I appreciate your feedback too. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>