-- check whether database owners are valid
select name into #check_database_owners
from master.dbo.sysdatabases
where isnull(suser_sname(sid),'no_owner') = 'no_owner'

IF SUBSTRING(@@version,23,4)='2000'
select SD.name as DBname,
suser_sname(SD.sid) as DBowner,
SL.name as USERname,
'Notfor2000' as LOGINname,
DD.name as DEFAULTdatabase
from master.dbo.sysdatabases SD left join master.dbo.sysxlogins SL
on SD.sid = SL.sid
left join master.dbo.sysdatabases DD on SL.dbid = DD.dbid
WHERE SL.name is null
ELSE
select SD.name as DBname,
suser_sname(SD.owner_sid) as DBowner,
SL.name as USERname,
SL.loginname as LOGINname,
SL.dbname DEFAULTdatabase
from master.sys.databases SD left join master.sys.syslogins SL
on SD.owner_sid = SL.sid
WHERE SL.name is null

CREATE TABLE #Results
([Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS)

SET NOCOUNT ON

DECLARE @DBName sysname, @Qry nvarchar(4000)

SET @Qry = ''
SET @DBName = ''

WHILE @DBName IS NOT NULL
BEGIN
SET @DBName = (SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
('master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind')
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName)

IF @DBName IS NULL BREAK
IF SUBSTRING(@@version,23,4)='2000'
SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
)'
ELSE
SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND (su.name <> ''guest''
AND su.name <> ''INFORMATION_SCHEMA''
AND su.name <> ''dbo''
AND su.name <> ''sys'')
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid
)'


print @Qry
INSERT INTO #Results EXEC (@Qry)
END

SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]

DROP TABLE #Results