0

Basic idea is I need to create a procedure, but I don't have access to the OPENQUERY linked server table so it errors out stating user does not have SELECT access. The idea is I'm trying to create the stored procedure and will have someone who does have access run it.

I've tried TRY/CATCH and that still provided the same issue. Is it due to the OPENQUERY? Any other way to ignore the error? Not sure why wrapping it in TRY/CATCH doesn't seem to ignore the error?

Basic code:

BEGIN TRY
    SELECT * 
    FROM OPENQUERY(SERVER_NAME,'SELECT ABC FROM ABC.DEF')
END TRY
BEGIN CATCH
END CATCH
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dulanic
  • 67
  • 1
  • 6
  • 1
    `TRY..CATCH` does nothing because resolution errors like these are part of the compilation process; your query doesn't even get to run because SQL Server can't determine an execution plan. Bottom line, you can't create the sproc if you don't have access. Why is this a problem, though -- why wouldn't you have a privileged account like a sysadmin/DB admin create the stored procedure in the first place? – Jeroen Mostert Apr 13 '16 at 13:34
  • We won't get into the crazieness that is our DBA teams at my work. The admins do create the proc in live production, this is just a dev area. I was able to work around this by wrapping the statements in EXEC('statement') as that doesn't calculate it that way. – Dulanic Apr 13 '16 at 14:18

1 Answers1

0

Was able to wrap the OPENQUERY /w EXEC instead which worked around this issue.

Dulanic
  • 67
  • 1
  • 6