List all databases of a certain user ( SQL Server 2008 R2 )

I'm really looking for this answer for some time. I bet there is a post here but, I really can't find ( even on google ).

I think it's a simple task.

I want to list all databases for a specific user.

Is there a way to do this?

It can be a view or a cursor. I just can't think of anything.

You can impersonate that login and use the HAS_DBACCESS built-in function.

USE master;
GO
EXECUTE AS LOGIN = N'your_login_name';
GO
SELECT name, HAS_DBACCESS(name) 
  FROM sys.databases;
GO
REVERT;

If you need to find the user mapping in each database (e.g. the user name in the database is not always the same as the login name), you can use dynamic SQL, e.g.

CREATE TABLE #db(name SYSNAME);
GO
use master;
GO
EXECUTE AS LOGIN = 'floob';
GO
INSERT #db(name)
  SELECT name FROM sys.databases
  WHERE HAS_DBACCESS(name) = 1
  AND database_id > 4; -- don't really care about system DBs, right?
GO
REVERT;
GO

DECLARE @sql NVARCHAR(MAX) = N'', @login SYSNAME = N'floob';

SELECT @sql += N'
  UNION ALL 
  SELECT login = @login, db = ''' + QUOTENAME(name) + N''', 
    username = p.name 
    FROM ' + QUOTENAME(name) + N'.sys.database_principals AS p
    WHERE sid = SUSER_SID(@login)'
  FROM #db;

SET @sql = STUFF(@sql, 1, 11, N'');
EXEC sys.sp_executesql @sql, N'@login SYSNAME', @login;
GO
DROP TABLE #db;
GO

StackExchange Database Administrators Q#108962, answer score: 9