Logshipping DR-Transfer Logins

Question

Logshipping DR-Transfer Logins

KKK17

SSChasing Mays

Points: 603
More actions
June 22, 2011 at 1:06 am

#253175

Hi Experts,
We know that LS is a HA concept by which we can maintain a copy of the database over mutiliple servers/instances. Is there any option/process/script to have all the logins that are associated to the Primary LS database needs to be created on Secondary server.
This may help when a real disaster occur and the primary server is completely down. We can get the database up but how can the users connect to DB when few logins are not transferred. In this case, we would manually create the missing logins and assign roles. But what if there are so many logins?
Please let me know any script or method to have all the logins sync with the Primary LS database.
Regards,
KKK

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

i use a script that extracts the logins using BCP from the live server to a file, the file is then exported to the DR server and a script on the DR server then inserts the users into the DR server

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

george sibbald SSC Guru Points: 104210 More actions · Answer 2

I thnk sp_help_revlogin is what you want.

I personally have a sqlagent job that produces the results of sp_help_revlogin which is then xcopied to the DR server.

If you can get the users assigned to windows groups and provide access to SQL via that group, then you will not have multiple logins to maintain.

---------------------------------------------------------------------

NickDBA Hall of Fame Points: 3792 More actions · Answer 3

Agreed. I've used sp_help_revlogin many times to transfer logins from a primary to secondary which is in read only standby mode for reporting purposes.

http://support.microsoft.com/kb/246133

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Sp_help_revlogin will script certain accounts that are unique to each machine, be sure to filter these out first.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Note that this is a manual process. If you add logins to the primary, it should be with a script, and you should amend the script to also add the login to the secondary (SQLCMD works well here)

george sibbald SSC Guru Points: 104210 More actions · Answer 6

but steve, with SQL logins they would end up with different SIDS and users would be orphaned on failover (and again on failback).

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

True, you could need to match up SIDs with SQL logins.

Likely PowerShell could somehow run the script, take the ouput and pipe that in as a new batch for a second server.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

The main issue you'll face is around windows accounts. As I already mentioned the stored procedure will script certain windows accounts that you don't want to apply to the target. Filter them first as trying to apply them will cause unnecessary errors!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

I posted a note and Jonathan Kehayias wrote this: http://www.sqlskills.com/blogs/jonathan/post/Creating-a-new-Login-on-Multiple-Servers-with-the-same-SID-using-Powershell.aspx

george sibbald SSC Guru Points: 104210 More actions · Answer 10

thanks steve.

---------------------------------------------------------------------

KKK17 SSChasing Mays Points: 603 More actions · Answer 11

Hi All,

Thanks a lot for all your prompt replies.

I knew that, sp_help_revlogin script can be used to transfer logins from one server to another. but my point is to a specific database.

I dont want to transfer all the logins to the secondary server.

The logins which are associated/mapped to a specific database eg.XYZ

the same logins needs to be created on secondary server.

Let me be very clear about it.. While we setup logshipping on a database, we will restore it with standby on secondary and will sync the logins..but later if few more logins are added mapped to the primary DB, those logins needs to be created on the secondary server automatically. It should look for existence first and then create..

This will help us in a situation where the primary server is totally crashed and we need to bring up the secondary DB up in a short time and can fix the application connectivity..

Regards,

KKK

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

You can modify Jonathan's script to check for logins that have a used in a specified database, or just make sure you manually add those logins. There isn't a way that SQL Server provides to automate this.

Note that if you transfer logins that do not have users in that database, and that database is restored, the users will not have access.

KKK17 SSChasing Mays Points: 603 More actions · Answer 13

Thanks Steve!!! I think the script provided by Jonathan will help me.. But I am not at familiar with Power shell scripts 🙁 .. I am not sure how to use and where to use it..

can you please guide me in using the said script so that I will plan to use that script in a job and run it on a schedule so that the logins will be in sync on both servers..

Thanks a lot in advance!!!

KKK

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 14

this might help you: http://www.sqlservercentral.com/articles/powershell/73288/

Powershell is a command line environment with SQL 2008 and newer Windows OSes. You can invoke the command line and then execute scripts, similar to how VBScript used to work.