Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2013-02-01 : 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-02 : 03:21:32
|
| why not create a proxy account for SSIS package to use and give it same rights as user.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Access to the remote server is denied because the