Search code examples
sql-serverprocedureopenquery

Error /w OPENQUERY access error in SQL Server create procedure. Possible to create anyway?

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
Solution

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