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_princ...