Fix orphan logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Fix orphan logins

Author

Topic

aatishpatel
Starting Member

8 Posts

Posted - 2014-02-12 : 10:06:54

I have SQL database which is in Standby mode. This database is a secondary database in Logshipping. I have some Server level logins and I would like to sync it with the Standby database. I tried lot and it doesn't allow me to map the database to the logins.
Does anyone have any idea how to sync logins in this case?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-12 : 12:14:22

You don't map the database to the logins on the secondary server. Instead, you make sure to use master database as the default database on the principle server and then use a script like this to copy the password/sid from the principle server to the secondary server: http://sqlmag.com/Articles/ArticleID/16090/16090.html. If you need to schedule a job, here's a blog I wrote a while back which addresses this issue: http://weblogs.sqlteam.com/tarad/archive/2008/06/24/How-to-transfer-SQL-logins-between-SQL-Server-2005-instances.aspx. Please note it was written for SQL Server 2005, but it will likely work for the newer versions or very minor changes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources