Month: June 2018

Now we all know that having SQL Server Agent jobs owned by ‘Real’ users isn’t a good idea. But I don’t keep that close an eye on some of our test instances, so wasn’t surprised when I spotted this showing up in the monitoring:

The job failed. Unable to determine if the owner (OldDeveloper) of job important_server_job has server access (reason: Could not obtain information about Windows NT group/user 'OldDeveloper', error code 0x534. [SQLSTATE 42000] (Error 15404)).

Wanting to fix this as quickly and simply as possible I just wanted to bulk move them to our job owning account (let’s use the imaginative name of ‘JobOwner’).

I have 2 ways of doing this quickly. First up is an old T-SQL script I wrote years back (yeah, this happens all the time)). In this case we’re looking for all the jobs owner by the user OldDeveloper and moving them to the JobAccount user. It uses a cursor, but they aren’t always the spawn of evil 🙂

DECLARE @job_id char(36)
DECLARE JobCursor CURSOR
FOR
SELECT job_id  FROM msdb..sysjobs sj 
    INNER JOIN sys.server_principals sp on sj.owner_sid=sp.sid
    WHERE sp.name = 'OldDeveloper'
OPEN JobCursor 
FETCH NEXT FROM JobCursor INTO @job_id
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
    @job_name = @job_id,
    @owner_login_name = 'JobAccount'
FETCH NEXT FROM JobCursor INTO @job_id
END 
CLOSE JobCursor 
DEALLOCATE JobCursor 

And then, of course, there’s a simpler dbatools method in PowerShell:

Get-DbaAgentJob -SqlInstance server\instance | Where-Object {$_.OwnerLoginName -eq 'OldDeveloper'} | Set-DbaAgentJob -OwnerLogin JobAccount

Use Get-DbaAgentJOb to get all the SQL Agent jobs from your instance, a quick pipe through Where-Object to filter down to just the ones with a ‘bad’ owner, and finally into Set-DbaAgentJob to set the new owner login.

You can use whichever you prefer as they’ll do the same job. I find the PowerShell/dbatools the easiest one as I can remember the syntax, the T-SQL one I have to remember where I left the original copy 😉

Had a developer wander across confused about where the SSIS package he’d just redeployed had gone. No errors when he’d deployed the package to Integration Services, but when he looked at the properties of the package (or at least the one he thought he’d uploaded) it was showing a deployment date of 3 weeks ago.

A handy feature of having SSIS now using SSISDB to store it’s packages is that we can now query it to find out where it had gone with some simple T-SQL. In this case all I needed to do was to ask:

select 
	ip.name, 
	id.name as 'FolderName', 
	ip.deployed_by_name, 
	ip.last_deployed_time 
from 
	internal.projects ip inner join internal.folders id on ip.folder_id=id.folder_id
order by 
	ip.last_deployed_time desc

And I could see that the last time he’d deployed it, it had gone into Folder Y rather than Folder X. So after a quick clean up and redeploy everything was as he expected.