Lowell (3/16/2009)
i just slapped this together.i'm using sp_revokeaccess instead of sp_dropuser, because the users might be orphaned and not tied to a login;
this yanks them out the way i expected:
[font="Courier New"]ALTER PROCEDURE sp_DeleteUsers(@DeleteThem INT = 0)
AS
BEGIN
DECLARE @username VARCHAR(64)
DECLARE c1 CURSOR FOR
SELECT name
FROM sysusers
WHERE name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
AND LEFT(name,3) <> 'db_'
OPEN c1
FETCH next FROM c1 INTO @username
WHILE @@fetch_status <> -1
BEGIN
PRINT 'Dropping ' + @username
IF @DeleteThem <> 0
EXEC dbo.sp_revokedbaccess @username
FETCH next FROM c1 INTO @username
END
CLOSE c1
DEALLOCATE c1
END
[/font]
Here is another way to do that:
declare @sql nvarchar(max)
set @sql = ''
SELECT @sql = @sql+
'
print ''Dropping '+name+'''
execute master.dbo.sp_revokedbaccess '''+name+'''
'
FROM
dbo.sysusers
WHERE
name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
AND LEFT(name,3) <> 'db_'
order by
name
execute ( @sql )