Run Built-In Function on linked server

Question

We have two SQL 2014 servers, and I have created a Linked Server on one of them to the other. As part of a task I'm trying to run I need to convert a SQL UserID to a username on both the local and the linked server.

On the local server I can just call

SELECT SUSER_NAME(1)

which will return the string sa

Is there an easy way to run the function SUSER_NAME on the linked server to get a value back from there?

The other alternative I think I've found is to do a join to sys.server_principals and pull out the name from there, but SUSER_NAME seems like a cleaner approach.

Answer 1

You can execute Dynamic SQL on that Linked Server by executing sp_executesql on that remote server using the 4-part name. Then you just assign that to a variable marked as OUTPUT:

DECLARE @RemoteName sysname,
 @LoginID INT;
SET @LoginID = 267;
EXEC [LinkedServerName].master.dbo.sp_executesql
 N'SET @tmpRemoteName = SUSER_NAME(@tmpLoginID);',
 N'@tmpLoginID INT, @tmpRemoteName sysname OUTPUT',
 @tmpLoginID = @LoginID,
 @tmpRemoteName = @RemoteName OUTPUT;
SELECT @RemoteName;

Of course, it is debatable as to whether this is "cleaner" than JOINing to that remote table as there are pros and cons to each approach.