SQL Server Agent not observing “execute as” permissions

Question

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?

Answer 1

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.

Answer 2

Executed as user: johndoe

The EXECUETE AS context is a user context, not a login context. As such it falls under the restrictions of EXECUTE AS sandboxing, see Extending Database Impersonation by Using EXECUTE AS:

when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default.

Specifically, it would not inherit any server level group membership derived permission unless the database is marked as trustworthy. Your choice is to either mark [MyDatabase] as trustworthy (thus making any db_owner member of [MyDatabase] a de-facto member of sysadmin, with all implications) or you can grant permissions explicitly to user [johndoe] in [MyDatabase].