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.

Login ‘Domain\abc’ owns one or more database(s). Change the owner of database(s) before dropping the login. (Microsoft SQL Server, Error:15174)

error 15174

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

Solution

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.
sp_helpdb

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.
change the database owner to sa
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.
Delete the schema
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.

Read More:

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *