Is is possible to find an equivalent Login in a 2nd database via a single query?

Question

A database-level Login can be associated with zero to one server-instance-level Logins. And if it exists, that in turn can be associated with zero to one database-level Logins on another database.

Is it possible to retrieve the matching Login using a single query?

Given the answer to this question, I suspect it's not. But it's worth asking.

Answer 1

Assuming you have a local database user named foo, you can use this query to find out if there is a related user in database [splunge]:

SELECT [local].[name], [remote].[name]
  FROM sys.database_principals AS [local]
  INNER JOIN [splunge].sys.database_principals AS [remote]
  ON [local].[sid] = [remote].[sid]
  WHERE [local].[name] = 'foo';

If you don't know which other database(s) the related login may be found in, then no, there isn't a simple way without constructing a query like the answer in the other question you pointed to. If you are going to use sp_msForEachDB, please use caution:

• Making a more reliable and flexible sp_MSforeachdb
• Execute a Command in the Context of Each Database in SQL Server

One way to do this a little easier would be:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + '
    UNION ALL SELECT ''' + QUOTENAME(name)
    + ''', name COLLATE SQL_Latin1_General_CP1_CI_AS
    FROM ' + QUOTENAME(name) + '.sys.database_principals
    WHERE sid IN (SELECT sid FROM x)'
    FROM sys.databases 
    WHERE database_id > 4; -- assume ignore system dbs

SET @sql = ';WITH x AS (SELECT sid FROM sys.database_principals 
    WHERE name = ''foo'')' + STUFF(@sql, 1, 12, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

This doesn't meet your requirement of "a single query" but maybe you could explain why that is a requirement.