SQL Server execute procedure as user -

i have stored procedure loads data target table , works fine when run in session ad credentials.

when try run in job (again ad details in run option) login not have access 1 of db's.

i used

exec sp1 

which worked fine.

i used (to emulate running stored procedure in job)

execute user = 'domain\jdoe'  execute sp1  revert 

which failed.

why stored procedure fail when running same credentials used in different session window?

thanks in advance

you need set source database trustworthy. note has other security implications (see below).

by default in sql server cannot use assumed security context out of 1 database , unless source trusted. setting database trustworthy how indicate database trusted source. security measure designed prevent hacks 1 database application (via injection, usually) using springboard of other databases in same sql server. setting trustworthy saying "this database is secure and no 1 can out isn't supposed to."

alter database statements 1 require no 1 else in database when alter it. can add rollback immediate end of command throw else out first. of course may have consequences of own ... ;-)