SSRS – Unable to determine if the owner of job has server access [SQLSTATE 42000] (Error 15404))

The job failed. Unable to determine if the owner (unresolveddomain\user) of job 316C6CFE-EB33-4C96-85D6-A593B2CD1C82 has server access (reason: Could not obtain information about Windows NT group/user ‘unresolveddomain\user’, error code 0x54b. [SQLSTATE 42000] (Error 15404)).

SSRS Subscriptions suddenly are not being delivered, and the above error message is found when checking SQL Agent.

When I check a report, it does not seem like the schedule even fires from SSRS, however when I check the SQL Server Agent, it seems several jobs are failing. To isolate the issue, I looked for commonalities in which user account owns the job.

Simply check to see if all the jobs which are failing are based on a single user account.

SELECT
job_id
,j.[name] AS [JobName]
,CASE WHEN j.Enabled = 0 THEN 'No' ELSE 'Yes' END as [Enabled]
,l.[name] AS 'OwnerName'
FROM
MSDB.dbo.sysjobs j INNER JOIN
Master.dbo.syslogins l ON j.owner_sid = l.sid
ORDER BY j.[name]

Subsequently, update each of these jobs with a user that has the required privilege, or which can be resolved by the system. Test on one job first. Be sure to write the requisite rollback script. In the event you attempted fix does not work, you don’t want to have another issue to investigate.

In this case, a development domain was re-built and this server no longer had access to the domain.

USE [msdb]
GO

DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_name NVARCHAR(128)
DECLARE @enabled NVARCHAR(128)
DECLARE @owner_name NVARCHAR(128)
DECLARE @sql NVARCHAR(4000)
DECLARE c_cursorJobs CURSOR
FOR
SELECT
job_id
,j.[name] AS [JobName]
,CASE WHEN j.Enabled = 0 THEN 'No' ELSE 'Yes' END as [Enabled]
,l.[name] AS 'OwnerName'
FROM
MSDB.dbo.sysjobs j INNER JOIN
Master.dbo.syslogins l ON j.owner_sid = l.sid
WHERE
l.[name] LIKE 'unresolveddomain\user'
ORDER BY j.[name]

FOR READ ONLY
OPEN c_cursorJobs
FETCH NEXT FROM c_cursorJobs INTO @job_id, @job_name,@enabled,@owner_name
WHILE(@@FETCH_STATUS=0)
BEGIN
--SET @sql = 'EXEC msdb.dbo.sp_update_job @job_id=N'''+ CAST(@job_id AS NVARCHAR(500)) + ''', @owner_login_name=N''domain\user'''
--EXEC sp_executesql @sql
--PRINT @sql

SET @sql = 'EXEC msdb.dbo.sp_update_job @job_name=N'''+ CAST(@job_name AS NVARCHAR(500)) + ''', @owner_login_name=N''domain\user'''
EXEC sp_executesql @sql
-- PRINT @sql

FETCH NEXT FROM c_cursorJobs INTO @job_id, @job_name,@enabled,@owner_name
END
CLOSE c_cursorJobs
DEALLOCATE c_cursorJobs

This can be implemented as a SQL Job on the server. However, if you run into this problem outside of your development environment, consider using some change management, or rebuilding your server 😛

Advertisements

One thought on “SSRS – Unable to determine if the owner of job has server access [SQLSTATE 42000] (Error 15404))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s