Fix SQL Server Error 15174: Login owns one or more database(s). Change the owner of database(s) before dropping the login.
I came across Microsoft SQL Server error 15174 while dropping a login. As error suggests, the login which i want to drop is set as owner for databases. Let us check the database owner and then change the ownership to different login of identified databases to fix this issue. You can also read other login deletion errors (Error 15173) in attached link.
Microsoft SQL Server Error 15174
I had to remove a domain login on one of SQL Server instance. Login was failed to drop because of SQL Server error 15174. Error details are given below.
As error clearly says that one or more databases are owned by this login so let us check the database owners. As my instance has only one user databases so i ran sp_helpdb command to get the details. If you have multiple user databases then you can run below command to check owner of all databases in one shot.
SELECT name, suser_sname(owner_sid) AS Database_Owner FROM sys.databases
As i have mentioned, there is only one database on my SQL Server instance so i had used sp_helpdb to check the database owner. Below is the screenshot of sp_helpdb. You can see the owner is the same login id which we are trying to drop in below image.
Now next step is to change the owner to some another login. I have changed it to sa as sa is system account and not an individual account. Run below command to change the owner of this databases to sa in order to fix this SQL Server error 15174.
USE DBNAME--Change DBNAME with your database name GO sp_changedbowner 'sa'
We can see command executed successfully. You can recheck the database owner to validate the changes. Once change is done, next step is to drop the login.
Go ahead and drop the login again. you can do it by either SSMS or T-SQL. SSMS way is given below:
- Connect to target SQL Server Instance.
- Expand the security folder.
- Expand the Logins folder.
- Right click and choose delete on the identified login which needs to be deleted.
- Click on Ok button of the login deletion window.
It will give you the below screen to further click on ok button. Once you will click on ok button of below screen, your login will be dropped and deletion window will disappear.
If you want to drop this login using T-SQL command then open an new query window and execute below command.
--change the name of Login_name with your login name. DROP Login 'LOGIN_NAME'
Login will be dropped post executing above query and you will not face Microsoft SQL Server error 15174 again. I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
- Fix SQL Server Error 15170: Login owns one or more Agent Jobs
- Fix SQL Server Error 15434: Could not drop login as the user is currently logged in
- How to Fix SQL Server Error 15141: The Server principal owns one or more endpoints and cannot be dropped
- Fix Login Drop issue Error 15138 and Error 3729
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018