Microsoft SQL server error 15174 occurs while dropping a login.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and see how to fix it.

More about SQL error 15174

Generally, this error occurs while trying to remove a domain login on one of the SQL server instances.

For instance, the error appears as 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)

Here the error message clearly indicates that one or more databases are owning this log in. Also, sometimes the login which we were trying to drop is set as the owner for the database. So this error occurs.

How we fix SQL error 15174

Here are the steps our Support Engineers follow to resolve this error message.

1. Check Database details

First, we check the details of the database.

If there is only one user database then we run the below command.

sp_helpdb

If there are multiple user database then we run the below command.

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

After collecting the details of the database, the next step is to change the owner of the database to another login.

2. Change the owner of the database

Here is the command we run to change the owner of the database to sa(that is system account).

USE DBNAME
GO
sp_changedbowner ‘sa’

Note: Change DBNAME with the database name

After the successful execution of the above command, we recheck the database owner to validate the changes. Then we drop the login.

3. Drop the login

Here are the steps that we follow to drop the login using SSMS (SQL Server Management Studio):

• Connect to target SQL Server Instance.
• In Object Explorer, go to ‘Security’ and after that expand the ‘Logins’ folder as well.
• Now, right-click on the SQL Server Login you want to drop then choose the ‘Delete’ option.
• SSMS will pop-up a warning message.
• Finally, click on the ‘Ok’ button to confirm the deletion.

In case, if you are willing to drop this login using the T-SQL command then open a new query window and run the below command.

DROP Login ‘LOGIN_NAME’

Note: Replace Login_name with your login name.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, this SQL error occurs while dropping a login. Today, we saw how our Support Engineers fix this SQL error.