Error Fix – Msg 15170This login is the owner of 1 job You must delete or reassign these jobs before the login can be dropped
We used to delete login/user in SQL Server for ex employees so I told one of my subordinate to delete login of past employee. He deleted all logins which I have provided in the list but he wasn’t able to delete one login so he came to me with following error.
Msg 15170, Level 16, State 1, Line 2
This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.
Well error is itself pretty clear and self explanatory that 1 job is belongs to that login and if we either delete that job or assign job to another login and then drop that login.
Let us see this practically.
I am going to create one login in my SQL Server and one user in AdventureWorks2012 database with following script.
USE [master] GO CREATE LOGIN [TempLogin] WITH PASSWORD=N'templogin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [AdventureWorks2012] GO CREATE USER [TempLogin] FOR LOGIN [TempLogin] GO
Now, I will create one JOB in SQL Server with following script and “TempLogin” login will be the owner of that job.
USE [msdb] GO DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'DeleteLoginTest', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'TempLogin', @job_id = @jobId OUTPUT select @jobId GO EXEC msdb.dbo.sp_add_jobserver @job_name=N'DeleteLoginTest', @server_name = N'USER12\SQL2K12DEV' GO USE [msdb] GO EXEC msdb.dbo.sp_add_jobstep @job_name=N'DeleteLoginTest', @step_name=N'TestingStep', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_fail_action=2, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'SELECT 1', @database_name=N'master', @flags=0 GO USE [msdb] GO EXEC msdb.dbo.sp_update_job @job_name=N'DeleteLoginTest', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @description=N'', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'TempLogin', @notify_email_operator_name=N'', @notify_netsend_operator_name=N'', @notify_page_operator_name=N'' GO
Now, let us trying to drop the login with following script.
USE [master] GO DROP LOGIN [TempLogin] GO
As soon as you will execute DROP LOGIN script given above, you will be greeted with error something like this:
--Msg 15170, Level 16, State 1, Line 2 --This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.
So, I have now two choices as given:
1.) Delete the job
2.) Change the owner of job
If job is no more required, we can simply delete that job but I don’t want to delete this job so I have one last option remain which is change owner of job. First I will find the name of the job which belongs to the login I wanted to delete.
SELECT job.job_id AS JobId, job.name AS JobName,logins.name AS LoginName FROM msdb.dbo.sysjobs AS job INNER JOIN sys.syslogins AS logins ON job.owner_sid = logins.sid GO
Here is the screen capture of above script.
Now, I know that “DeleteLoginTest” job belongs to TempLogin so I am going to change owner of this job. SA login will be new owner of my “DeleteLoginTest” job.
USE [msdb] GO EXEC msdb.dbo.sp_update_job @job_id=N'422078c9-0b63-4e9e-b1da-89b02cab4af3', @owner_login_name=N'sa' GO
Please note that you have to provide “@job_id” and you can get it from SELECT script given above.
As soon as “msdb.dbo.sp_update_job” system stored procedure gets executed our job owner will be changed and now we will be able to drop the login with following script.
USE [AdventureWorks2012] GO DROP USER TempLogin GO USE [master] GO DROP LOGIN [TempLogin] GO
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.