So I expanded a little on your original script by creating the create and drop statements. The create statements are commented out but are there in case you delete a user that you really wanted.
/*************************************************
** Purpose: To return database users (for each db) orphaned from any login.
** Created By: James Howard
** Created On: 03 DEC 09
** Modified By: Bob Cole
** Modified On: 29 Dec 09 - Added script to render create and drop statements.
** The create statements are commented out but are there in case you delete a
** user that you really wanted.
*************************************************/
--create a temp table to store the results
CREATE TABLE #temp (
DatabaseName NVARCHAR(50),
UserName NVARCHAR(50)
)
--create statement to run on each database
declare @sql nvarchar(500)
SET @sql='select ''?'' as DBName
, name AS UserName
from [?]..sysusers
where (sid is not null and sid <> 0x0)
and suser_sname(sid) is null and
(issqlrole <> 1) AND
(isapprole <> 1) AND
(name <> ''INFORMATION_SCHEMA'') AND
(name <> ''guest'') AND
(name <> ''sys'') AND
(name <> ''dbo'') AND
(name <> ''system_function_schema'')
order by name
'
--insert the results from each database to temp table
INSERT INTO #temp
exec SP_MSforeachDB @sql
--return results
--SELECT * FROM #temp
SELECT 'USE ' + DatabaseName + CHAR(10)
+ '--GO ' + CHAR(10)
+ '--CREATE USER ' + UserName + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA=[' + UserName + ']' + CHAR(10)
+ '--GO ' + CHAR(10)
+ '' + CHAR(10)
+ 'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + UserName + ''')' + CHAR(10)
+ 'BEGIN' + CHAR(10)
+ 'DROP SCHEMA [' + UserName + ']' + CHAR(10)
+ 'DROP USER [' + UserName + ']' + CHAR(10)
+ 'END'
+ 'GO' + CHAR(10)
+ '' + CHAR(10)
+ '--------------------------------------------------------------------------------' + CHAR(10)
+ '' + CHAR(10)
FROM #temp
DROP TABLE #temp
----------------------------------------------------------------------------