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

same role name for mirroring setup? Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 1:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 1,068, Visits: 3,032
Hi,

1. Principal & mirror server result the same role, Is it correct for FULL safty configuration without wittness?

SELECT role 
FROM sys.database_mirroring_endpoints;

Role = 1 (partner)

2. Grantor is different on principal & mirror server, also same domain account running for SQL service account & Login account,

SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;

--Principal server result

--Name : Mirroring
--STATE : G
--GRANTOR: servername\Administrator
--PERMISSION: co
--GRANTEE: domainname\mydomain account

(Grantee filed- SQL service account should display instead of my domain id)

--Mirror server result

--Name : Mirroring
--STATE : G
--GRANTOR: sa
--PERMISSION: co
--GRANTEE: domainname\mydomain account

(Grantee filed- SQL service account should display instead of my domain id)


Pl. suggestion me what could be issue display same role name and different grantor, grantee name?

thanks


Post #1354344
Posted Wednesday, September 5, 2012 1:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:13 PM
Points: 6,466, Visits: 13,919
ananda.murugesan (9/5/2012)
Hi,

1. Principal & mirror server result the same role, Is it correct for FULL safty configuration without wittness?

SELECT role 
FROM sys.database_mirroring_endpoints;

Role = 1 (partner)

both instances are partners in the mirror session so this is valid




ananda.murugesan (9/5/2012)
2. Grantor is different on principal & mirror server, also same domain account running for SQL service account & Login account,

SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;

--Principal server result

--Name : Mirroring
--STATE : G
--GRANTOR: servername\Administrator
--PERMISSION: co
--GRANTEE: domainname\mydomain account

(Grantee filed- SQL service account should display instead of my domain id)

--Mirror server result

--Name : Mirroring
--STATE : G
--GRANTOR: sa
--PERMISSION: co
--GRANTEE: domainname\mydomain account

(Grantee filed- SQL service account should display instead of my domain id)


Pl. suggestion me what could be issue display same role name and different grantor, grantee name?

thanks

Not sure what the question is here, this query details users who have been granted connect to the mirroring endpoint


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1354352
Posted Thursday, September 6, 2012 12:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:42 PM
Points: 1,618, Visits: 1,549
To be a mirror or principal partner, the endpoint's role has to be either "partner" or "all". Both can be "partner, both can be "all" or one can be "partner" while the other is "all". That's all fine.

Grantor is informational only and has no net effect on the mirroring session. It does not matter who granted it.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1355038
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse