主題: [mssql]remove a db owner login

[netman]

Ref:
http://coresql.com/2013/10/24/cant-drop-user-the-server-principal-owns-one-or-more-endpoints-and-cannot-be-dropped/

Scenario:
1.   Used user-A to create DB, and assigned to db owner.
2.   Need to remove user-A and to use user-B instead.

Symbols:
1.   Can’t remove user-A and encountering following error 15141:
The server principal owns one or more endpoint(s) and cannot be dropped hence unable to delete a login from SQL Server

Steps:
1.   Open SSMS and add new Login user-B, assign db owner.
2.   Open DB properties then select File Permission, change owner to user-B.
3.   Run query to find all endpoints related to user-A:

代碼: [選擇]

SELECT p.name, e.* FROM sys.endpoints e
inner join sys.server_principals p on e.principal_id = p.principal_id4.   Run alter to change owner:

代碼: [選擇]

Alter Authorization on endpoint::Mirroring to user-B5.   Go to Security/Login to remove user-A

[netman]

Also, changing the SCCM part:

Ref:
https://social.technet.microsoft.com/Forums/en-US/ef4fdcd1-bd69-4462-a654-558d048d3352/sccm-2012-super-admin-deleted?forum=configmanagergeneral

1. query and get the IDs:
use CM_XXX
select AdminID,AdminSID,LogonName,DisplayName from dbo.RBAC_Admins

#result example:

代碼: [選擇]

    AdminID    AdminSID                                 LogonName     DisplayName
1  1234        0x0105000......ABC000               user_A            user_A
1  5678        0x0105000......DEF001               user_B            user_B

2. Replace user_A's info with user_B's

代碼: [選擇]

use CM_S01
update dbo.RBAC_Admins
set AdminSID=0x0105000......DEF001,LogonName='user_B',DisplayName='user_B'
where AdminID=1234