3

I have a stored procedure which loads data into a target table and works fine when run in a session with my AD credentials.

When I try to run it in a job (again with my AD details in the Run As option) suddenly the login does not have access to one of the DB's.

I used 

EXEC SP1

Which worked fine.

I used (to emulate running the stored procedure in a job)

EXECUTE AS user = 'Domain\JDoe'

EXECUTE SP1

REVERT

Which failed.

Why does the stored procedure fail when running with the same credentials which are used successfully in a different session window?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Powell21
  • 147
  • 1
  • 14
  • Have you considered running your SQL Job or job steps as a specific (domain) user instead of defining it in your execution? – Jens Nov 17 '16 at 14:28
  • @Jens, yes, that was my first attempt. I checked the job history and noticed that the error message said i did not have the correct access to one of the necessary DB's. Upon seeing that I ran the SP manually with the EXEC line which worked so then I tried the EXEC AS with my username and it failed with the same error as the job – Powell21 Nov 17 '16 at 14:30
  • Which dbms are you using? – jarlh Nov 17 '16 at 14:30
  • What error do you get when it fails? – Alec. Nov 17 '16 at 14:37
  • Can you run execute as inside stored proc – TheGameiswar Nov 17 '16 at 14:37
  • @Alec. "The server principal "Domain\JBlogs" is not able to access the database "SourceDB" under the current security context." – Powell21 Nov 17 '16 at 14:41
  • @TheGameiswar I guess I could but I don't see how that will help me as the issue I'm having is that I can run the DB manually but it fails when i use a job or EXEC AS. If I had an account that could run it successfully with EXEC AS it would help but I'm afraid I don't – Powell21 Nov 17 '16 at 14:42
  • You need to set the source database to `TRUSTWORTHY`. Note that this has other security implications. – RBarryYoung Nov 17 '16 at 14:45
  • If you want to run it from a SQL Agent Job, you can define an Agent Proxy that has all of the access rights it needs and then run it under that proxy in the Agent. This would get around having to use TRUSTWORTHY. – RBarryYoung Nov 17 '16 at 14:49
  • @RBarryYoung, thanks very much, could you possibly elaborate on that or point me to somewhere online that could please? – Powell21 Nov 17 '16 at 14:49
  • @RBarryYoung, having the job run with a proxy was the first plan but it failed unfortunately. – Powell21 Nov 17 '16 at 14:51
  • @RBarryYoung, I tried to set the `TRUSTWORTHY` property but the source DB is our production CRM DB and I can't get a lock – Powell21 Nov 17 '16 at 14:55
  • By default in SQL Server you cannot use an *assumed* security context to get out of one database and into another unless the source is trusted. Setting a database to TRUSTWORTHY is how you indicate that the database is a trusted source. This is a security measure designed to prevent someone who hacks into a database from an application (via Injection, usually) form then using that as a springboard into all of the other databases in the same SQL Server. By setting it to TRUSTWORTHY you are saying "this database is *secure* and no one can get out who isn't supposed to." – RBarryYoung Nov 17 '16 at 14:57
  • Alter database statements like this one require that no one else is in the DB. You can add `WITH ROLLBACK IMMEDIATE` to the end of the command to throw everyone else out first. – RBarryYoung Nov 17 '16 at 14:59
  • @RBarryYoung Thanks for your help. i cant kill all connections now but I will do it first thing tomorrow or overnight and see if that fixes my issue. Thank you all very much for your help! – Powell21 Nov 17 '16 at 15:00
  • Guess I'll add this as an aswer ... – RBarryYoung Nov 17 '16 at 15:01
  • @RBarryYoung I'm happy to do it and credit it to you once I have been able to test, it's up to you – Powell21 Nov 17 '16 at 15:06

1 Answers1

4

You need to set the source database to TRUSTWORTHY. Note that this has other security implications (see below).

By default in SQL Server you cannot use an assumed security context to get out of one database and into another unless the source is trusted. Setting a database to TRUSTWORTHY is how you indicate that the database is a trusted source. This is a security measure designed to prevent someone who hacks into one database from an application (via Injection, usually) from then using that as a springboard into all of the other databases in the same SQL Server. By setting it to TRUSTWORTHY you are saying "this database is secure and no one can get out who isn't supposed to."

Alter database statements like this one require that no one else is in the database when you ALTER it. You can add WITH ROLLBACK IMMEDIATE to the end of the command to throw everyone else out first. Of course that may have consequences of its own ... ;-)

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137