boumerlin,

I have the same issue as you stated in this thread. The difference is that I want to drop all of these "orphaned" users that exist in AD, but still are considered orphaned since they are not linked to a defined login.

Here is the script that I used to this.

-- Return database users (for each db) orphaned from any login.

-- results table

CREATE TABLE #orphy (DatabaseName NVARCHAR(128), UserName NVARCHAR(128))

declare @sql nvarchar(500)

SET @sql=

'Select ''?'' as DBName, sdp.name as UserName

From [?].sys.database_principals sdp

Left Join [?].sys.server_principals ssp On sdp.sid = ssp.sid

Where ssp.sid is null

and sdp.type in (''S'',''U'',''G'')

and sdp.name not in

(''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''BROKER_USER'', ''dbo'')'

--insert the results from each database to temp table

INSERT INTO #orphy exec SP_MSforeachDB @sql

SELECT * FROM #orphy

-- Use this to generate the Drop schema and drop user; (results to text)

Select 'USE [' + DatabaseName + ']' + char(13) + char(10) + 'GO' + char(13) + char(10)

+ 'IF EXISTS (SELECT * FROM sys.schemas WHERE name = N' + '''' + UserName + ''')' + char(13) + char(10)

+ 'DROP SCHEMA [' + UserName + ']' + char(13) + char(10) + 'GO' + char(13) + char(10)

+ 'DROP USER [' + UserName + ']' + char(13) + char(10) + 'GO'

from #orphy Order by DatabaseName