CREATE VIEW sys.database_mirroring_endpoints AS
SELECT
e.name,
endpoint_id = e.id,
principal_id = o.indepid,
e.protocol,
protocol_desc = p.name,
e.type,
type_desc = t.name,
state = convert(tinyint, e.bstat & 3), -- BSTAT_STATEMASK
state_desc = s.name,
sysconv(bit, case when e.affinity = -1 then 1 else 0 end) AS is_admin_endpoint,
role = convert(tinyint, e.tstat & 7), -- TSTAT_DM_ROLEMASK
role_desc = cr.name,
sysconv(bit, case when e.encalg = 0 then 0 else 1 end) AS is_encryption_enabled,
-- Authentication and encryption options. Identical in SSB and DBM endpoints
connection_auth = e.authtype,
connection_auth_desc = ca.name,
certificate_id = ISNULL(co.indepid, 0),
encryption_algorithm = e.encalg,
encryption_algorithm_desc = ag.name
FROM master.sys.sysendpts e
LEFT JOIN sys.syssingleobjrefs o ON o.depid = e.id AND o.class = 60 AND o.depsubid = 0 -- SRC_ENDPTLOGINOWNER
LEFT JOIN sys.syspalvalues p ON p.class = 'EPPR' AND p.value = e.protocol
LEFT JOIN sys.syspalvalues t ON t.class = 'EPTY' AND t.value = e.type
LEFT JOIN sys.syspalvalues s ON s.class = 'EPST' AND s.value = e.bstat & 3
LEFT JOIN sys.syspalvalues cr ON cr.class = 'EPMR' AND cr.value = e.tstat & 7
LEFT JOIN sys.syspalvalues ca ON ca.class = 'EPCA' AND ca.value = e.authtype
LEFT JOIN sys.syspalvalues ag ON ag.class = 'EPCE' AND ag.value = e.encalg
LEFT JOIN sys.syssingleobjrefs co ON co.class = 25 and co.depid = e.id AND co.depsubid = 0 -- SRC_ENDPT_CERTIFICATE
WHERE e.type = 4
AND has_access('HE', e.id) = 1
|
