SQL SERVER – FIX: Msg 15170, Level 16, This login is the Owner of 1 Job(s). You Must Delete or Reassign these Jobs Before the Login can be Dropped

In our real life, there are lots of dependencies and same is true with SQL Server as well. This blog also talks about inability to drop a login due to a dependency. Let us see the error related to login is the owner of the 1 job(s).

When you try to drop a login from SQL Server, you might run into the error. Here is what I received when I was trying to drop a login called “foo” from the SQL Server Management Studio.

This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped. (Microsoft SQL Server, Error: 15170)

If the same action is tried from below T-SQL:

USE [master]
GO
DROP LOGIN [foo]
GO

Then we get this:

Msg 15170, Level 16, State 1, Line 4
This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.

WORKAROUND/SOLUTION

As mentioned in the error message, this is due to dependency of job on the account which we are trying to delete. We need to find out such jobs so that suggested action can be taken.

SELECT name,* 
FROM msdb..sysjobs
WHERE owner_sid = SUSER_SID('foo')

You need to replace “foo” which the user name which you are trying to delete. The output of the query would show the jobs which are owned by this login. The next action would be to change the job owner as shown below.

Have you faced any similar errors? If this blog has helped you in learning something new, please comment as let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)