Solve SQL error 15174

SQL Issues and Solutions

In the process of dropping a Login, we sometimes experience SQL Server error 15174 when another database owns the Login.
As part of our Server Support Services here at Ibmi Media, we frequently help our customers to fix SQL related problems.

In this context, we shall look into the main causes of this error and how to solve it.

What to expect when you experience SQL error 15174?

When this error occurs, you will see an error message like;

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

This signifies that the Login is owned by one or more databases. Therefore, when another user owns the Login which we are trying to drop, this error will be triggered.

Steps to fix SQL error 15174?

To solve this error, follow the following steps accordingly;

i. Inspect the details of the Database.

To begin, the details of the database should be inspected. For a single database, you can use the command below to do this;

sp_helpdb

Whereas, for multiple user databases, the following command should be used;

SELECT name, suser_sname(owner_sid) AS Database_Owner FROM sys.databases

Now that we know more about the database, it is time to modify the owner of the database to a different one which is suitable.

ii. Modifying the owner of the database

To change the owner of the database, simply use the command below;

USE DB_NAME

GO

sp_changedbowner ‘sa’

Here we are changing the owner to the system account also known as "sa". DB_NAME in the above command represents the name of the database. So change it as per your case.

After changing the owner of the database successfully, you can now proceed with the dropping process. See how to drop the login in the next step.

iii. Dropping the Login

To drop the Login, you can either use SQL Server Management Studio (SSMS) or T-SQL script command. We will explain both below.

Dropping via SSMS.
Follow the instructions below to drop via SSMS;

1. Make a connection to the SQL Server Instance.
2. Next, Go to the "Security" in the Object Explorer followed by expanding the "Logins" folder.
3. Here, do a right-click on the SQL Server Login you intend dropping and choose the "Delete" option from the list.
4. A popup message will be displayed on the SSMS windows which you should authenticate by clicking "OK" to effect the deletion of the Login.

Dropping via T-SQL
To drop using the T-SQL Script command, open the query window and use the command below;