August 12, 2015 at 8:48 am
I am attempting to assist a client of ours. We are a software vendor and client has chosen to use database mirroring for redundancy.
The client is using SQL Server 2012. This was a migration from SQL 2005 to SQL 2012 due hardware retirement.
Our software requires a SQL user account to be DBO of the database.
The same user name / password was created on both primary and mirror servers.
The DB was setup on both primary and mirror machines (restore from same backup) taken from retired SQL server.
The SQL user on primary was given DBO. sp_changedbowner sql_dbo
The failover was initiated.
The SQL user on mirror was given DBO.
Upon the fail back test, the SQL user on Primary has lost membership to the DB. This was easily restored by re-running sp_changedbower sql_dbo again.
I read this article here SSC which appears to describe a very similar issue, minus the DBO status.
My question:
Is it possible to not require this re-assignment of user rights after the failover each time? Thus, removing the need for operator intervention.
If so, how should that be done?
August 12, 2015 at 8:55 am
You need to create the login with the same SID on each server. The login-to-user mapping is on SID, not name.
Get the script sp_help_revlogin from Microsoft (a search will turn it up, it's in a KB article) and use that to script the login on the principle, then drop the login on the mirror and run the CREATE LOGIN script generated on the principle.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2015 at 2:59 am
Hi,
Gila is right. That would fix your issue. Is not critical but do not forget assigning the same server roles to the underlying user login object too just in case there are other external references.
August 14, 2015 at 9:26 am
If both logins/users exist on both PRD/DR, then you just need to do the orphan user fix.
Regards,
SQLisAwe5oMe.
August 14, 2015 at 10:14 am
SQLisAwE5OmE (8/14/2015)
If both logins/users exist on both PRD/DR, then you just need to do the orphan user fix.
Yes, but unless he fixes the root problem (the mismatched SIDs), he'll have to do that every time the mirroring is failed over.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply