SQL SERVER – Unable to drop login – Msg 15170, Level 16, State 1

SQL
No Comments

Encountering error messages and solving them is something I often do. I think each and every error we encounter is validating our understanding in using the software and how we make simple mistakes. Let us learn about what we should do when we are unable to drop login.

As you might know, I do a lot of public speaking and many demos are done on my laptop. Due to which there are many unused objects in my SQL Server instance. To get my SQL instance, in a clean state, I was trying to clean up the object and when I was trying to drop a login, I got below error:

DROP LOGIN [danny]
GO

SQL SERVER - Unable to drop login - Msg 15170, Level 16, State 1 job-owner-01

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

I first thought the error message is very self-explanatory to start with. The error message is clear that I need to modify the job which is owned by this login. Finding the job was easy for me as I had only a few jobs. I was able to double click on each job and look at the owner in the UI.

SQL SERVER - Unable to drop login - Msg 15170, Level 16, State 1 job-owner-02

On second thoughts, I felt – what if the server was some production database and had far too many jobs? What would a potential DBA do? So, if you have a server which has lots of job, opening properties of each job would not be a fun. Here is the script which can be used to find such jobs. You need to replace the login name with the appropriate name on your servers.

SELECT NAME
FROM msdb.dbo.sysjobs
WHERE owner_sid IN (
SELECT sid
FROM MASTER.sys.syslogins
WHERE NAME = 'danny'
)

SQL SERVER - Unable to drop login - Msg 15170, Level 16, State 1 job-owner-03

To modify the owner either UI can be used or T-SQL. I have used below command to change the owner to sa.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name =N'foo', @owner_login_name=N'sa'
GO

After this I was able to drop the login. Have you ever been in this situation before? What did you do? Did you do something manually or wrote a similar script? Do let me know via comments.

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

, , ,
Previous Post
SQL SERVER – Error and Fix for Msg 1907 Cannot recreate index The new index definition does not match the constraint being enforced by the existing index
Next Post
SQL SERVER – How To Improve Performance by Offloading Backups to a Secondary Replica – Notes from the Field #108

Related Posts

Leave a Reply

Menu