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?
msdb
to run Agent jobs? – Simon Righarts Sep 11 '12 at 6:40