SQL Server Agent not observing "execute as" permissions

I have a curious permissions problem. Something recently happened that caused existing SQL Server Agent Jobs that were configured to run as a particular User to stop working. They previously worked; they are now throwing basic permissions errors (e.g., SELECT permission denied).

Here's a specific test case. The user johndoe has the server-level sysadmin role. The following query works fine when run by johndoe in SQL Server Management Studio:

select * from TableA into TableB


But when I put that exact same query into a single-step SQL Server Agent Job, with that step configured to run as user johndoe, I get the following error.


Executed as user: johndoe. The SELECT permission was denied on the
object 'TableA', database 'MyDatabase', schema 'dbo'. [SQLSTATE 42000]
(Error 229). The step failed.

Any suggestions on what might have caused this seemingly spontaneous change?

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data. If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.

StackExchange Database Administrators Q#24068, answer score: 3