Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Slightly off-the-wall suggestion - does johndoe have the appropriate permissions/group memberships in msdb to run Agent jobs? –  Simon Righarts Sep 11 '12 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. –  Craig Efrein Sep 11 '12 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 '13 at 8:20
add comment

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:

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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