In SQL Server 2008 I created 2 simple procedures, one which has a TRUNCATE table option. I have sysadmin rights on the server. DBO is the owner of both procedures.

I wanted a limited user to be able to execute the one with the TRUNCATE statement, so I added WITH EXECUTE AS OWNER into the sp. This user was now able to execute it from SSMS.

It seemed everything was OK until I wrote SSIS packages for them and ran batch files for them supplying a username and password in the connection string. Now the procedure I added WITH EXECUTE AS OWNER to comes back with the aforementioned error:

"Access to the remote server is denied because the current security context is not trusted"

If I change it back, a limited user will get the error that they cannot TRUNCATE the table.

How can I solve this contradiction? There are no linked servers here and it uses SQL Server authentication.

So, to summarize, and I tested it this way:

1) If I create the procedure WITH EXECUTE AS OWNER, the limited user can run it with the TRUNCATE statement, but it WON'T run with SSIS without the error above.

2) If I remove the WITH EXECUTE AS OWNER, the user gets an error, but the SSIS job can run successfully.