Hi,

I would not rely too much on sp_MSforeachdb because it might skip one or several databases on a busy server.

Also I would not skip dbo either, dbo can be mapped to a login other than sa therefore, when it happens you want to know about it.

Please find below the query I use to get all the databases users:

DECLARE @SQLCMDDBUser NVARCHAR(MAX)

IF object_id('tempdb..#DBUserTable','U') IS not NULL DROP TABLE #DBUserTable

CREATE TABLE #DBUserTable

([ServerName] [sysname] NOT NULL,

[DBName] [sysname] NOT NULL,

[UserName] [sysname] NOT NULL,

[MappedLogin] [nvarchar](128) NULL,

[UserType] [nvarchar](60) NOT NULL,

[CreateDate] [datetime] NULL,

[ModifyDate] [datetime] NULL,

[IsOrphan] [int] NOT NULL)

SELECT @SQLCMDDBUser=coalesce(@SQLCMDDBUser,'') + CHAR(13) + CHAR(10) + ' use ' + QUOTENAME([name]) + ';

INSERT INTO #DBUserTable

SELECT

@@ServerName As ServerName,

DB_NAME() As DBName,

DP.name As UserName,

SP.name As MappedLogin,

DP.type_desc AS UserType,

DP.create_date As CreateDate,

DP.modify_date As ModifyDate,

Case

WHEN SP.sid Is Null THEN 1

ELSE 0

END As IsOrphan

FROM ' + QUOTENAME([name]) + '.sys.database_principals DP LEFT JOIN master.sys.server_principals SP ON DP.sid = SP.sid

WHERE

DB_Name() NOT IN (''model'',''tempdb'') AND

DP.type NOT IN (''R'')AND

DP.is_fixed_role <> 1 AND

DP.sid NOT IN (0x01,0x00) AND

DP.sid IS NOT NULL AND

DP.name NOT LIKE ''##%''

ORDER BY DBName,UserName;' FROM master.sys.databases WHERE name NOT IN ('tempdb','model') AND state_desc = 'ONLINE' ORDER BY name

EXECUTE(@SQLCMDDBUser)

SELECT * FROM #DBUserTable

DROP TABLE #DBUserTable

Regards.