Fix SQL Server Error 15170: Login owns one or more Agent Jobs
I got Microsoft SQL Server error 15170 while removing a SQL Server login on one of SQL Server Instance. Microsoft SQL Server Error 15170 suggests that the login which i want to drop owns one or more SQL Server Agent jobs. Here we will fix this issue by changing the owner of all identified jobs and then will drop the SQL login.
If you are facing different kind of DROP login failed errors, you can read below articles where i have demonstrated step by step method to fix the various kind of DROP login failed errors:
- How to fix Error 15174: Login owns one or more databases.
- How to fix Error 15173: Revoke the permissions before dropping the login.
- How to fix error 15138 & Error 3729: DROP login failed
- How to fix error 15141: The server principal owns one or more endpoint(s) and cannot be dropped
- How to fix error 15434: Could not drop login as the user is currently logged in
Microsoft SQL Server Error 15170
I had to remove a SQL login on one of SQL Server instance where AlwaysOn Availability Group is configured and running. We need to remove this login and recreate it with the same SID which are present on primary replica to fix an AOAG Automatic failover issue. Login was failed to drop because of SQL Server error 15170 which suggests that this login owns one or more SQL Server Agent jobs. Error details are given below.
The login is the owner of 3 job(s). You must delete or reassign these jobs before the login can be dropped. (Microsoft SQL Server, Error:15170)
As error clearly says, that one or more Agent jobs are owned by this login so let us check the details about it. Expand SQL Server Agent followed by Jobs folder of your SQL Server Instance connected in SSMS. You can see below jobs were there on SQL Server Instance.
Solution
Now check the properties of these agent jobs to check the job owner. Double click on identified agent job. You will get below image. Here you can see job owner is showing as same account which we are trying to drop. Change it to sa and click on ok button to apply the change. Repeat the same to the all three agent jobs.
Now owner of agent jobs is changed to sa. Next step is to 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.
If you want to drop this login using T-SQL command then open a query window and execute below command.
--change the name of Login_name with your login name which you want to delete. DROP Login 'LOGIN_NAME'
This time your login will be dropped successfully. If you are still facing some another issue during dropping this login, you can read below articles where i have demonstrated step by step method to fix the DROP login failed errors:
- How to fix Error 15174: Login owns one or more databases.
- How to fix Error 15173: Revoke the permissions before dropping the login.
- How to fix error 15138 & Error 3729: DROP login failed
- How to fix error 15141: The server principal owns one or more endpoint(s) and cannot be dropped
Here, we have fixed Microsoft SQL Server error 15170. I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
