Drop Failed for User - Error MSSQLSERVER 15421

发布时间：2020-12-12 15:21:32 所属栏目：MsSql教程来源：网络整理

导读：In a?SQL Server 2005 database,I was having a hard time deleting a user I had created. I kept getting this error: The database principal owns a database role and cannot be dropped.? Msg 15421. MSDN and Google were not helpful on this error.

In a?SQL Server 2005 database,I was having a hard time deleting a user I had created.

I kept getting this error:

The database principal owns a database role and cannot be dropped.? Msg 15421.

MSDN and Google were not helpful on this error. Ater lots of search attempts I kept coming up empty:

Msdn No Results Msg 15421

I spent some time looking through various dialogs in?SQL Server Management Studio. I was?unable to find the problem?- probably because I am not as familiar with the UI as I was with Enterprise Manager.

I finally wrote a?script that helped me identify for which role the user was listed as an owner. Here it is:

select dp2.name as role,dp1.name as owner
from sys.database_principals as dp1 inner join sys.database_principals as dp2
on dp1.principal_id = dp2.owning_principal_id
where dp1.name = 'DeleteMe '

In the last line of the script,make sure you specifiy the user name and not the login name. 'DeleteMe' is the user name I want to delete. See the screen shot below:

Database User Dialog

After running this script,I found which role had my user listed as owner.

Results

With that knowledge,I opened the role dialog in SQL Server Management Studio and changed the owner to 'dbo'. Below is the before screen shot.

Database Role Properties Dialog - Before

The owner should be changed to a principal other than the one you are trying to delete. I used 'dbo' as shown here:

new owner

Once this change was made?I was able to delete the user I wanted to get rid of.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!