SQL Server 2014 – How to Run Built-In Functions on Linked Server

functionslinked-serversql serversql server 2014

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.

Best Answer

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.

Related Solutions

Sql-server – How does one query a SQL Server Linked Server when SQL Server Authentication is specified

Open the properties of your linked server. Select Security, and click the "be made using this security context" radio button. Put your SQL Server login in this section. Clear out any logins listed in the box above "Local server login to remote server login mappings", and try again.

Run ‘USE’ with Linked Server Alternatives in SQL Server

You can use EXEC to run dynamic SQL in a different database or different server. Also please try to use the catalog views directly instead of all of these messy metadata functions.

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = N''Table_1''
      AND c.name = N''column1'';';

EXEC mylinkedserver.testdb.sys.sp_executesql @sql;

Even better, parameterize it:

DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = @t
      AND c.name = @c;';

EXEC mylinkedserver.testdb.sys.sp_executesql 
  @stmt = @sql,
  @params = N'@t SYSNAME, @c SYSNAME', 
  @t = @t, 
  @c = @c;

Best Answer

Related Solutions

Sql-server – How does one query a SQL Server Linked Server when SQL Server Authentication is specified

Run ‘USE’ with Linked Server Alternatives in SQL Server

Related Question