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
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.
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' )
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)
