Linked server with Execute as Self

• douglascfast

  Right there with Babe

  Points: 776

  More actions

  December 24, 2008 at 7:48 am

  #125991

  So here are the facts:

  I have my SQL server linked into a Teradata server.

  I can run my queries just fine.

  Trauma area:

  I want to create procedures that other people can run using my log in to Teradata, but don't want them to have access to the link. So I want to write the procedure so it executes as the person who created it (Me).

  No I can run this just fine.

  Select * from openquery (Teradata, "

  Select Current_Date

  ")

  And if I do it as a produre

  Create Procedure DUMBDOUG AS

  Select * from openquery (Teradata, "

  Select Current_Date

  ") END

  All is good. When I try this

  Create Procedure DUMBDOUG

  WITH EXECUTE AS SELF AS

  Select * from openquery (Teradata, "

  Select Current_Date

  ") END

  I get this:

  Msg 15274, Level 16, State 1, Procedure *********

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

  In the end I am hoping other teams can run the procedure with out giving them access to the link.

  I am sure this is quite complicated as it requires lots of setting I most likley do not know. Please let me know if I need to post more information on this.

  Happy Holidays to all.

  Doug

• Levi Justus

  SSCommitted

  Points: 1923

  More actions

  January 6, 2009 at 12:02 pm

  #922030

  Alter database DBNAME Set trustworthy ON

  
  Jesus My Saviour

• douglascfast

  Right there with Babe

  Points: 776

  More actions

  January 7, 2009 at 5:11 am

  #922440

  Thanks for this. I did some research over the holidays and found that my issue was two fold.

  One was your answer below. Setting the database to "Trustworthy" The second was that the database was copied from 2000 into 2005. I guess when this happens the Security ID stays the same and thus is not assigned in the new DB. So I also had to assign the copied database to a proper owner.

  So for anyone else I did this:

  Alter Database MyDB set trustworhy on

  Alter Authorization on database::MyDB to SA (The default)

  I never could get the Execute as Self to work but all is working when I use Execute as 'MyuserID'

  Thanks again for the reply

  Doug