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?