August 28, 2009 at 11:44 am
So I have an inline table function and a scalar function on a remote server that I am trying to execute. I am able to execute the table function with the following code:
SELECT * FROM OPENQUERY(LinkedServer,'SELECT * FROM DB.dbo.fn_GetCurrentTerms()')
When I try to execute the scalar function with this code:
SELECT * FROM OPENQUERY(LinkedServer,'SELECT DB.dbo.fn_GetNextTerm()')
I get this error:
OLE DB provider "SQLNCLI" for linked server "LinkedServer" returned message "Deferred prepare could not be completed.".
Msg 229, Level 14, State 5, Line 1
The EXECUTE permission was denied on the object 'fn_GetNextTerm', database 'DB', schema 'dbo'.
How do I execute this scalar function from another server?
Thanks in advance.
August 28, 2009 at 12:01 pm
The error points to a security issue. Are you sure it's NOT a permissions problem? You'd have to look at the linked server definition to figure out who your login is mapped to on the remote side to see if it has permissions to execute the function.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 28, 2009 at 1:14 pm
Well I am SA on both boxes, and I was the one who created the functions. The table function works fine from the same remote box that I am trying to call the scalar function.
August 28, 2009 at 2:46 pm
Yeah guess it was permissions. The linkedserver user I am using only had datareader on the remote box. What is weird though, the table function doesn't have execute permissions on the function and it works, and its in the exact same DB as the scalar function.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply