March 23, 2009 at 2:00 pm
I have 2 servers running IIS and SQL server. Server P is Primary (Principal) and Server S is secondary (mirroring). Whenever server P is down I have to execute
EXEC sp_change_users_login 'update_one', ' ', ' ' command.
Is there any way I can make it working automatically. So if Server P goes Down S will become Primary server.
All help would be appreciated.
Thanks
Rishabh
March 23, 2009 at 2:26 pm
To avoid having to use sp_change_users_login transfer the logins to server 'S' using MS supplied proc sp_help_revlogin. google that you will find it easy or go straight to technet.
---------------------------------------------------------------------
March 23, 2009 at 3:16 pm
I don't think is possible to run that sp on standby mode.
* Noel
March 23, 2009 at 3:46 pm
noeld (3/23/2009)
I don't think is possible to run that sp on standby mode.
which SP?
---------------------------------------------------------------------
March 23, 2009 at 4:29 pm
which SP?
sp_change_users_login
* Noel
March 23, 2009 at 4:39 pm
it would be possible once the mirror database had been bought online.
---------------------------------------------------------------------
March 24, 2009 at 6:11 am
Run sp_helprev_login on the main server.
Apply the script on the standby server.
Because the SIDS will match up you won't need to run any update to the users in the databases.
March 24, 2009 at 6:52 am
Nicholas,
Can you tell me how to run "sp_helprev_login " and how to apply script on standby server?
Am pretty new to SQL server.
March 24, 2009 at 7:14 am
Look for sp_help_revlogin on support.microsoft.com there are all the scripts and full instructions for usage there.
March 24, 2009 at 7:55 am
I checked at Support.microsoft.com
here is the link http://support.microsoft.com/kb/918992
in the remarks it said
"If server A and server B are in different domains, you have to modify the output script. Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins, click the following article number to view the article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/ ) How to resolve permission issues when you move a database between servers that are running SQL Server "
http://support.microsoft.com/kb/240872/
it said we need to run sp_change_users_login. Which I don't want to run. So can any one suggest me wat to do now??
March 24, 2009 at 8:06 am
rishgup (3/24/2009)
I checked at Support.microsoft.comhere is the link http://support.microsoft.com/kb/918992
in the remarks it said
"If server A and server B are in different domains, you have to modify the output script. Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins, click the following article number to view the article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/ ) How to resolve permission issues when you move a database between servers that are running SQL Server "
http://support.microsoft.com/kb/240872/
it said we need to run sp_change_users_login. Which I don't want to run. So can any one suggest me wat to do now??
are you in this situation? sp_change_users_login is for SQL authenticated users only which will not be affected by the different domain so you won't need to run it.
If you are on 2005 run alter user...with login as the URL describes for integrated logins.
why don't you want to run sp_change_users_login?. sometmes you have no choice but to fix orphaned users.
---------------------------------------------------------------------
March 24, 2009 at 8:12 am
I have to run sp_change_users_login on Secondary server every time Primary server goes down. I am looking for automatic solution that when ever Primary goes down. Secondary will start working as primary without executing sp_change_users_login.
March 24, 2009 at 8:36 am
if you use sp_help_revlogin then will never be necessary to run sp_change_users_login.
If the two servers are in different domains or you use local windows accounts, you will need to fix those accounts. No way round that but you can script it up.
---------------------------------------------------------------------
March 24, 2009 at 8:41 am
Primary goes down you want secondary to stand in.
Do you have cluster this will resolve your needs.
Also log shipping too.
You then don't copy all the logins over it all happens for you.
On cluster if one server goes down it rolls over to the 2nd one.
Same with log shipping too if i recall....
Or even data mirroring?
What happens if data is changed on primary server are you coming these databases over too.
March 24, 2009 at 8:45 am
Yes,
I have Mirroring ON. Only problem is I have to run sp_change_users_login everytime to make Secondary work as Princiapal when Primary is down.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply