SQLServer修改Availability Group Endpoint的属主

1.查看endpoint_name，以及当前Availability Group Endpoint的属主

use [master];
select suser_name(principal_id) as endpoint_owner,
name as endpoint_name
from sys.database_mirroring_endpoints;

这里查出的Database Mirroring endpoint名称是Hadr_endpoint。

 

2查看当前属主的授权情况

USE [master];
SELECT ep.name,
sp.STATE, 
SUSER_NAME(sp.grantor_principal_id) AS [GRANT BY],
sp.TYPE AS PERMISSION,
SUSER_NAME(sp.grantee_principal_id) AS [GRANT TO]
FROM sys.server_permissions sp, sys.endpoints ep
WHERE sp.major_id = ep.endpoint_id AND [name] = 'Hadr_endpoint';

　　

3.修改属主，并授权

BEGIN TRANSACTION
USE [master];
ALTER AUTHORIZATION ON ENDPOING::Hadr_endpoint TO sa;
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [domain\account];
COMMIT TRANSACTION;

这里将属主修改成sa；
domain\account就是第二步查出来的"grant to"的值。