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?

  • Slightly off-the-wall suggestion - does johndoe have the appropriate permissions/group memberships in msdb to run Agent jobs? Sep 11, 2012 at 6:40
  • Do you get the same error for all Agent Jobs or just the ones configured to run as johndoe? I would create a new user, add him to the sysadmin role and try the same experiment. At least try to exclude the SQL Server Agent Service account as the possible culprit. Sep 11, 2012 at 6:43
  • Follow up to my original question... The problem went away as mysteriously as it appeared. I think something was refreshed or re-saved, but I cannot say what.
    – dlh
    Jan 15, 2013 at 8:20

2 Answers 2


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:


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.


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].

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.