June 22, 2011 at 1:06 am
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
June 22, 2011 at 2:42 am
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" 😉
June 22, 2011 at 6:45 am
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.
---------------------------------------------------------------------
June 24, 2011 at 8:29 am
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.
June 24, 2011 at 4:01 pm
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" 😉
June 24, 2011 at 5:12 pm
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)
June 24, 2011 at 5:26 pm
but steve, with SQL logins they would end up with different SIDS and users would be orphaned on failover (and again on failback).
---------------------------------------------------------------------
June 24, 2011 at 5:30 pm
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.
June 25, 2011 at 1:06 am
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" 😉
June 25, 2011 at 3:44 pm
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
June 25, 2011 at 4:01 pm
thanks steve.
---------------------------------------------------------------------
June 26, 2011 at 1:28 am
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
June 26, 2011 at 7:52 am
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.
June 27, 2011 at 2:03 am
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
June 27, 2011 at 9:04 am
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.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply