SQL Server execute procedure as user

Question

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

Valuer

Have you considered running your SQL Job or job steps as a specific (domain) user instead of defining it in your execution? – Swedenborgian

@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 – Valuer

Which dbms are you using? – Manvell

What error do you get when it fails? – Autogenous

Can you run execute as inside stored proc – Motorway

@Autogenous "The server principal "Domain\JBlogs" is not able to access the database "SourceDB" under the current security context." – Valuer

@Motorway 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 – Valuer

You need to set the source database to TRUSTWORTHY. Note that this has other security implications. – Testimony

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. – Testimony

@RBarryYoung, thanks very much, could you possibly elaborate on that or point me to somewhere online that could please? – Valuer

@RBarryYoung, having the job run with a proxy was the first plan but it failed unfortunately. – Valuer

@RBarryYoung, I tried to set the TRUSTWORTHY property but the source DB is our production CRM DB and I can't get a lock – Valuer

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." – Testimony

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. – Testimony

@Testimony 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! – Valuer

Guess I'll add this as an aswer ... – Testimony

@Testimony I'm happy to do it and credit it to you once I have been able to test, it's up to you – Valuer

Answer 1

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 ... ;-)

Testimony