技術討論區 > database 討論版

[mssql]remove a db owner login

(1/1)

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_id
--- 程式碼結尾 ---
4.   Run alter to change owner:

--- 代碼: ---Alter Authorization on endpoint::Mirroring to user-B
--- 程式碼結尾 ---
5.   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

--- 程式碼結尾 ---

導覽

[0] 文章列表