Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Access to the remote server is denied because the
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

273 Posts

Posted - 02/01/2013 :  18:11:36  Show Profile  Reply with Quote
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?


Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/02/2013 :  03:21:32  Show Profile  Reply with Quote
why not create a proxy account for SSIS package to use and give it same rights as user.

SQL Server MVP

Edited by - visakh16 on 02/02/2013 03:22:17
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000