I like this script and find it useful for generating audit reports; however there are a few enhancements I would like to propose:
1. Any SQL Server object with a hyphen will cause the script to fail, so the script should be altered to use square brackets [] to enclose any potential SQL Server objects with hyphens. For example, a SharePoint database could be named SharePoint_AdminContent_2cb093ab-dbf9-4777-85d1-a867bfd62484. The hyphens in the DB name cause the script to fail unless it is referred to as [SharePoint_AdminContent_2cb093ab-dbf9-4777-85d1-a867bfd62484].
Alter the script as follows anywhere you see the DBName:
2005
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
etc.
2000
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
etc.
2. Make the DBName column larger than varchar(50) and LEFT function the DBName in the select statement with the amount of characters you created the table with so your insert is guaranteed to work. As it stands right now if you have a DNName larger than 50 characters the script will fail
Alter the script as follows:
--Create the temp table with a larger DBName column
CREATE TABLE #tuser (
DBName VARCHAR(100),
--******************************************************
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
LEFT(''?'', 100) as DBName,
3. Add server name to the output. If you are running this for auditing purposes and you run this on several machines, you'd want the server name in the output for when you put all the report together.
Alter the script as follows:
CREATE TABLE #tuser (
ServerName VARCHAR(100),
DBName VARCHAR(100),
--******************************************************
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
LEFT(@@SERVERNAME, 100) as ServerName,
LEFT(''?'', 100) as DBName,
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
etc.