|
duanecwilson
Constraint Violating Yak Guru
USA
266 Posts |
 Posted - 02/01/2013 : 18:11:36
|
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.
What gives here?
Duane |
|
Access to the remote server is denied because the
