4

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.

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.