While removing SQL Server login on one of the SQL Server Instance error 15170 occurs.

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 how to fix it.

Why does SQL Server error 15170 occur

Now let’s see more about this error message.

This error tells us that the login we are trying to drop is owned by more than one SQL Server Agent Jobs.

However, we can easily fix this error by changing the owner of all the identified jobs. Then we can drop the SQL logins.

For instance, the error appears as below.

This error clearly mentions that the login is owned by 3 Agent jobs.

How we fix SQL Server error 15170

Recently, one of our customers approached us with a similar error.

Now, let’s see how our Support Engineers fix this error to our customers.

1. Initially, we expand the SQL Server Agent followed by the Jobs folder of your SQL Server Instance connected in SSMS.

2. Then, we check the properties of these agent jobs to check the job owner.

3. Here we double click on the identified agent job. After that, we check whether the job owner is showing as same as the account that we are trying to drop.

4. We change the job owner and click on the ok button to apply the changes. Also, we repeat the same to all other agent jobs.

5. Now since the agent jobs are changed to some other name we next go ahead and drop the login again.

This can be either done by SSMS or T-SQL. The SSMS way is as below:

• First, connect to the target SQL Server Instance.
• Here, expand the security folder and Login folder.
• Now, right-click and choose delete on the identified login which needs to be deleted.
• Finally, click on the Ok button of the login deletion window.

Here is the query that we run to drop the login using the T-SQL command.

DROP Login ‘LOGIN_NAME’

NOTE: Do change the name of Login_name with your login name which you want to delete.

Now, the login will be dropped successfully.

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

Conclusion

In short, this error occurs while we try to drop a login that is already owned by more than one SQL Server Agent Jobs. Today, we saw how our Support Engineers fix this error to our customers.