作者 主題: [mssql]remove a db owner login  (閱讀 1173 次)

0 會員 與 1 訪客 正在閱讀本文。

netman

  • 管理員
  • 俺是博士!
  • *****
  • 文章數: 17333
    • 檢視個人資料
    • http://www.study-area.org
[mssql]remove a db owner login
« 於: 2015-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
« 上次編輯: 2015-07-29 14:11 由 netman »

netman

  • 管理員
  • 俺是博士!
  • *****
  • 文章數: 17333
    • 檢視個人資料
    • http://www.study-area.org
Re: [mssql]remove a db owner login
« 回覆 #1 於: 2015-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