TSQL to get the list of associated users from all databases for SQl server Login

Question

I am looking for tsql query to get all the associated users in all dbs for a login. Tried below SQL, but this is not giving right result set.

If I execute this query under the Database context where the user is present, it gives output as if user is present in all DBs.

Same query executed under the Database context where the user is absent, gives 0 rows as if user is not present in any of the DBs.

exec sp_msforeachdb 'select  ''?'' AS DBName,dp.name as username from sys.database_principals dp join sys.server_principals 
srp on dp.sid=srp.sid where srp.name=''domain\account'''

Thanks

Answer 1

You can use the following Dynamic SQL to build the query you need to hit all databases and get a list of Users:

DECLARE @LoginName AS NVARCHAR(100) = N'Your Login Name Here'
DECLARE @DynamicSQL AS NVARCHAR(MAX) = N'';
SELECT @DynamicSQL = @DynamicSQL +
    'SELECT ''' + [name] + ''' AS DatabaseName, [name] AS UserName FROM [' + [name] + '].sys.database_principals WHERE [type] IN (''S'', ''U'', ''E'', ''G'') AND [name] = ''' + @LoginName + ''' UNION ALL ' 
FROM sys.databases
WHERE [state] = 0; -- Online databases only
SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10); -- Cleans up unneeded trailing "UNION ALL"
-- PRINT @DynamicSQL -- Prints the dynamic SQL string (For Testing)
EXEC master.sys.sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL string

I based it off the query Aaron Bertrand is using in the answer that @Learning_DBAdmin linked, but I figured re-writing my own Dynamic SQL could make it a little more readable. (And as he mentions sp_msforeachdb is undocumented and unreliable, so better to query dynamically.)

This can be used to get SQL Users, Windows Users, Azure Active Directory External Users, and Windows Groups based on the Type column in sys.database_principals (you can adjust this as needed).