same role name for mirroring setup?

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  September 5, 2012 at 1:04 am

  #266331

  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

• Perry Whittle

  SSC Guru

  Points: 233821

  More actions

  September 5, 2012 at 1:32 am

  #1533650

  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" 😉

• Robert Davis

  One Orange Chip

  Points: 28027

  More actions

  September 6, 2012 at 12:22 am

  #1534235

  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[/url]
  SQL Server Best Practices: SQL Server Best Practices
  Twitter: @SQLSoldier
  My book: Pro SQL Server 2008 Mirroring[/url]
  Microsoft Certified Master: SQL Server, Data Platform MVP
  Database Engineer at BlueMountain Capital Management[/url]