酷!學園

技術討論區 => database 討論版 => 主題作者是: netman 於 2015-07-29 14:09

主題: [mssql]remove a db owner login
作者: netman2015-07-29 14:09
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_id
4.   Run alter to change owner:
代碼: [選擇]
Alter Authorization on endpoint::Mirroring to user-B5.   Go to Security/Login to remove user-A
主題: Re: [mssql]remove a db owner login
作者: netman2015-07-29 16:30
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