--minor changes so it works on a case-sensitive server
USE master
GO
BEGIN
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8'
begin
IF EXISTS (SELECT TOP 1 * FROM tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%')
begin
DROP TABLE #TUser
end
end
ELSE
begin
IF EXISTS (SELECT TOP 1 * FROM tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%')
begin
DROP TABLE #TUser
end
end
CREATE TABLE #TUser (
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT)
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSforeachdb
'
SELECT
''?'' as DBName,
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in (''public'',''dbo'',''guest'',''sys'')
ORDER BY u.name
'
ELSE
INSERT INTO #TUser
EXEC sp_MSforeachdb
'
SELECT
''?'',
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
JOIN [?].sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN [?].sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R'' and u.name not in (''public'',''dbo'',''guest'',''sys'')
order by u.name
'
SELECT *
FROM #TUser
ORDER BY DBName,
[Name],
GroupName
DROP TABLE #TUser
END