When the cheers subsided and the parade was over (oh yes, there was a parade; complete with floats and bagpipes), the DBA found herself dissatisfied. There was a minor detail that was nagging at her. What about those linked servers that used an alias? With her original query, the remote server would be the alias name, and that may or may not be helpful. Surely she could do better.
So our heroine searched the Windows registry until she found where all of the alias information was stored, and she wrote a function that used the xp_regread extended stored procedure to retrieve the target server for each alias.
IF OBJECT_ID('fn_getaliastarget', 'FN') IS NOT NULL
DROP FUNCTION fn_getaliastarget
GO
create function dbo.fn_getaliastarget (@alias varchar(40))
returns nvarchar(250)
AS
BEGIN
declare
@regbase nvarchar(30)
,@regkey nvarchar(100)
,@dirbasepath nvarchar(250)
SET @regbase = 'HKEY_LOCAL_MACHINE'
SET @regkey = 'SoftwareMicrosoftMSSQLServerClientConnectTo'
EXECUTE master..xp_regread @regbase
, @regkey
, @alias
, @dirbasepath OUTPUT
RETURN substring(@dirbasepath,CHARINDEX(',',@dirbasepath)+1,LEN(@dirbasepath))
END
GO
Then she revised her query to use the new function.
SELECT @@servername as [SQLInstance] , s.name as [LinkedServer] , ISNULL(dbo.fn_getaliastarget(s.data_source), s.data_source) as [RemoteServer] , CASE l.uses_self_credential WHEN 1 THEN 'UNMAPPED LOGINS' ELSE ISNULL(p.name, 'UNMAPPED LOGINS') END AS [LocalLogin] , CASE l.uses_self_credential WHEN 1 THEN 'USE SELF' ELSE l.remote_name END AS [RemoteLogin] FROM sys.linked_logins l JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1 LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id ORDER BY [LinkedServer], [LocalLogin]
And finally, the DBA wrote a version of the query for SQL Server 2000 instances, too, just for good measure.
SELECT @@servername as [SQLInstance] , s.srvname as [LinkedServer] , ISNULL(dbo.fn_getaliastarget(s.datasource), s.datasource) as [RemoteServer] , ISNULL(p.name, 'UNMAPPED LOGINS') AS [LocalLogin] , ISNULL(l.rmtloginame, 'USE SELF') AS [RemoteLogin] FROM master..sysoledbusers l JOIN master..sysservers s ON s.srvid = l.rmtsrvid AND dataaccess = 1 AND isremote = 1 LEFT OUTER JOIN master..syslogins p ON p.sid = l.loginsid ORDER BY [LinkedServer], [LocalLogin]
And she was happy. And awesome.