Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"Set Partner Failover" "User must be in the master database"? Expand / Collapse
Author
Message
Posted Wednesday, November 20, 2013 9:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:19 AM
Points: 86, Visits: 539
I'm trying to switch roles in a mirroring session from mirror to principal using the following statement on the principal.

alter database [dbname] Set Partner Failover;

I get the following error message

Msg 5001, Level16, State 1, Line 1
User must be in the master database.

The command executes fine on the original principal and failover to the mirror happens flawlessly.
The command fails when executing on the 'mirror' server when that database has the Principal role.

I am able to failback to the original principal using the GUI though.

Any ideas why I get that message doing the role switch via T-SQL??
Post #1516100
Posted Wednesday, November 20, 2013 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 8:26 AM
Points: 288, Visits: 542
Are all your SQL logins that exist on the principal server available on the instance hsoting the mirror which are used by your mirrored database?

MCITP SQL 2005, MCSA SQL 2012
Post #1516105
Posted Wednesday, November 20, 2013 9:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:19 AM
Points: 86, Visits: 539
The logins match on both servers.
Post #1516124
Posted Wednesday, November 20, 2013 10:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 8:26 AM
Points: 288, Visits: 542
For the SQL Logins (opposed to the windows logins) do they have the same SID?

MCITP SQL 2005, MCSA SQL 2012
Post #1516128
Posted Wednesday, November 20, 2013 10:47 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 5,850, Visits: 12,599
are you connected to the master database when you issue the command?

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

Post #1516139
Posted Thursday, November 21, 2013 9:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 5,850, Visits: 12,599
george sibbald (11/20/2013)
are you connected to the master database when you issue the command?


as this has not received a response what I meant by this was you must be in the context of the master database when issuing the command

alter database [dbname] Set Partner Failover

that is what the error message

Msg 5001, Level16, State 1, Line 1
User must be in the master database.

is referring to


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

Post #1516496
Posted Friday, November 22, 2013 6:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 48, Visits: 258
could this script fix this problem?

use master
alter database [dbname] Set Partner Failover

i am not sure if it works for a mirror failover

Post #1516757
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse