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

Question

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.

Answer 1

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

Answer 2

You can use System Stored Procedure sp_msloginmappings which show account mapping information in all databases

-- Create Temp Table
CREATE TABLE #loginmappings (
    LoginName nvarchar(max),
    DBName nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

-- Execute System Stored Procedure 
INSERT INTO #loginmappings 
EXEC master..sp_msloginmappings 


--- query Required Result Set
SELECT LoginName, Username, DBName
FROM   #loginmappings 
where LoginName='bruna' -- 'YourLoginName'
ORDER BY dbname, username


-- Drop Temp Table 
DROP TABLE #loginmappings