SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

duanecwilson
Constraint Violating Yak Guru

USA
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?

Duane

visakh16
Very Important crosS Applying yaK Herder

India
52309 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
http://visakhm.blogspot.com/


Edited by - visakh16 on 02/02/2013 03:22:17
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.04 seconds. Powered By: Snitz Forums 2000