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:

Solarwinds
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.

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 drop-foo-01-800x378

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – New Quality of Database Documentation – Dataedo
Next Post
SQL SERVER – Unable to Restore from URL – The Specified URL Points to a Block Blob. Backup and Restore Operations on Block Blobs are not Permitted

Related Posts

2 Comments. Leave new

  • 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.
    I had this messaging error for quire some time,but after calling the job with this query like you suggested and replacing (foo) with my job login (name) I was finally able to locate the job and delete it..

    Thank you Man, this + to my experience.

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

    Reply
  • Easy, short and to the point answer. Thank you!

    Reply

Leave a Reply

Menu