Encountering error messages and solving them is a common occurrence when working with SQL Server. Each error we encounter serves as a validation of our understanding in using the software and highlights the simple mistakes we may make. In this article, we will discuss what to do when you are unable to drop a login in SQL Server.

As a frequent public speaker, I often perform demos on my laptop, resulting in the accumulation of unused objects in my SQL Server instance. To ensure my SQL instance is in a clean state, I decided to clean up these objects. However, when attempting to drop a login, I encountered the following error:

DROP LOGIN [danny]
GO

Error Message: 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.

At first glance, the error message seemed self-explanatory. It clearly indicated that I needed to modify the job owned by the login in question. Since I only had a few jobs, I was able to easily identify and modify the owner by double-clicking on each job in the UI.

However, I then considered what a potential DBA would do if they were dealing with a server that had numerous jobs. Manually opening the properties of each job would be a tedious and time-consuming task. To address this, I developed a script that can be used to identify such jobs:

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

In the script above, you would need to replace ‘danny’ with the appropriate login name on your servers. This query retrieves the names of all jobs owned by the specified login.

To modify the owner, you can either use the UI or T-SQL. In my case, I used the following 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 modifying the owner, I was able to successfully drop the login.

Have you ever encountered a similar situation? If so, how did you handle it? Did you manually modify the jobs or write a similar script? I would love to hear your experiences and insights in the comments below.