Error Management is a key task in any project or task that you handle.

Since the SQL Server 2005 version the Management Tools (SQL Server Management Studio) equipped with majority of tools to obtain such information related to the instance or database. One of them and my favourite is Job Activity Monitor, which obtains the data from sysjobactivity table internally and also enable you to set filters to limit number of jobs that you want to monitor. Similar to this you can also use sp_help_jobactivity to obtain such information from query editor, to get such information within the SSMS tool you can  expand SQL Server Agent in Management Studio Object Explorer, right-click Job Activity Monitor, and click View Job Activity.

Automating Administration was one of the key recipe in my book SQL Server 2008 R2 Administration cookbook, The automated administration of multiple SQL Server instances requires a pool of resources and multiple utilities to manage. Since SQL Server 2005 Service Pack 2 (SP2), the multiple instance management feature is introduced and using SQL Server agent services for the central administration of jobs. Managing multiple instances will increase the total cost of ownership (TCO) and software footprint. 

To enable the Multiserver administration environment, we need more than one SQL Server instance that will stand as master server and target server. A word of caution that in order to enable Multiserver administration we need  setup of a master server (MSX) and one or more target servers (TSX). The Multiserver administration is possible between SQL Server 2008 R2 and lower versions, the least TSX version can only be until SQL Server 2000 SP3. The security settings for SQL Server and SQL Server agent services, which require a Windows domain account are an important requisite in Multiserver administration.

Similarly you can also perform the Multiserver administration using Master Server wizard. This plays an essential part in setting up automated administration on a Multiserver environment. Multiserver administration requires a master server and one or more target servers, so that the jobs will be processed on defined target servers. The wizard will perform the process to check the security settings for SQL Server Agent service and SQL Server service on all servers intended to become target servers.

Coming back to the error management recently I had to correct the issue on Master server which involves a batch process (ETL) on multiple servers, this is scheduled as a job on master server. The error generated was:

Message

'EXECUTE AS USER' failed for the requested user '<<UserName>>' in the database '<<DBName>>'.  The step failed.

The error message clearly indicates that specified user on that database has no permission to execute the specified task. This was perplexing as this job was running fine since last few months since it was implemented. Few generic thoughts on mind to solve this was to check database TRUSTWORTHY, check permission for that user on database and any recent changes on database access privileges or jobs.

The first point of TRUSTWORTHY is worth enough to mention, as the name mentions this is used to indicate whether the SQL instance trusts the database and contents within it. By default this setting is OFF, and need to set ON using ALTER DATABASE statement. For obvious reasons you must be part of SYSADMIN group to play with database and instance level setting. This leads to Security which is a default from version 2008 onwards (that  doesn't mean earlier versions aren't secured). SQL 2008 take advantage of Operating System User Account Control (UAC) that are set since Windows Server 2008 that will reduce the surface and attack area by comprising the 'least privileged' and increases the separation of Windows Administrators and DB Administrators. Further to this topic I recommend to read Using Kerberos Authentication and EPA (Extended Protection for Authentication).

The first check of TRUSTWORTHY database option was passed as it is essential for this database to function which is essential on other databases to access. The section check of obtaining permission level for the user was also passed as it is DB owner.

Finally the recent changes in the access privileges was not applicable for this user as this is used exclusively by the scheduled jobs. Further a change that was made inside the job step was to use EXECUTE AS USER or LOGIN option. Also found that recently the database owner was changed from a login to SA. So there is a setting required on authorization to access the database, hence the ALTER AUTHORIZATION option is used to change ownership of a securable. 

ALTER AUTHORIZATION

   ON [ <class_type>:: ] entity_name
   TO { SCHEMA OWNER | principal_name }
[;]

Finally this setting enabled the job to complete successfully without any error, I would also recommend to understand on how SQL Server Agent and security uses the method of job step execution and blog post about SQL Agent jobs & user contexts by Tibor Karaszi.