Ellie's blog

若SQL Service Accont為Windows內建服務帳號(比方 NT Service\MSSQLSERVER)，需在遠端建立對方機器名稱的SQL login並授尋endpoint 權限

CREATE LOGIN [ELLIE\sql5$] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [ellie\SQL21$];
GO
 

可使用下列T-SQL

--確定endpoint 有啟動
select M.name, M.endpoint_id, M.principal_id, P.name, M.state_desc  from sys.database_mirroring_endpoints M
join sys.server_principals P
on M.principal_id = P.principal_id

--且權限有授與對方機器的SQL service 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;   
GO  

 

 

select * From sys.endpoints
select * from sys.database_mirroring_endpoints
select * from sys.tcp_endpoints