How to remove user from login (user is already part of group) and keep the jobs?

Question

I have a sql login for windows group.

One of the members of ths group was also added as a sql login. This user has 1 job in which he is registered as the owner.

I want to remove this user login because he is already part of group membership.

If I try to delete the user (assuming he will retain all his permissions via the group membership), then it gives an error that there is 1 job owned by this user.

How to remove this login, and keep the jobs, and also ensure that the user retains permission that it has inherited via the group membership?

Answer 1

Modify the job ownership first so that the group login owns the job, then drop the login.

Sample T-SQL that accomplishes that is:

USE msdb;
--change ownership
EXEC dbo.sp_update_job @job_name = N'some job', @owner_login_name = N'the_group_login';
GO

--drop the unneeded login
USE master;
DROP LOGIN [the_individual_login];
GO

When a job is created by someone logging in via a Windows Login or Windows Group, the owner is set to the name of the Windows principal name. This is one of many reasons why it's important to specify the job owner at job creation time. I typically choose to set the owner to sa even if that account is disabled and even if SQL Server Authentication is set to Windows Only.