List all databases of a certain user ( SQL Server 2008 R2 )
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 &gt; 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