Find a specific user across all databases in a SQL instance using SP_MSForeachdb.

The following is used to find a specific user across all databases in a SQL instance using SP_MSForeachdb.  Roles for each user are concatenated per database in one row.  A user can be listed multiple times

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

DECLARE @DB_USers TABLE (DBName sysname, UserName sysname, LoginType sysname, MemberRole varchar(max),create_date datetime,modify_date datetime) INSERT @DB_USers EXEC sp_MSforeachdb  'use [?] SELECT ''?'' AS DB_Name, case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName, prin.type_desc AS LoginType, isnull(USER_NAME(mem.role_principal_id),'''') AS MemberRole ,create_date,modify_date FROM sys.database_principals prin LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%''' SELECT dbname,username ,logintype ,create_date ,modify_date, STUFF((SELECT ',' + CONVERT(VARCHAR(254),Memberrole) FROM @DB_USers user2 WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName FOR XML PATH('')) ,1,1,'') AS Permissions_user FROM @DB_USers user1 WHERE user1.UserName LIKE '%vall%' ----- UPDATE this row to a like name you are looking for GROUP BY dbname,username ,logintype ,create_date ,modify_date ORDER BY DBName,username

Omit the following users:

– No Guest Acxcounts, Skip fixed database roles such as DB_OWNER , db_datawriter, db_datareader and Principals with NULL SID ; those are internal to the DB such as INFORMATION_SCHEMA & SYS listed as ##

SYSADMINS are not part of a users list as they have access to everything on server. You can get list SYSADMIN users by running following code

1 2 3 4 5

SELECT p.name AS [Name] ,r.type_desc,r.is_disabled,r.create_date , r.modify_date,r.default_database_name FROM sys.server_principals r INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id WHERE r.type = 'R' and r.name = N'sysadmin'

– If a windows login is part of a windows group then you may need to look at the members of this Windows group to identify who can access this database.