|
create proc
usp_remove_orphan_users as
-- Written by: Gregory A.
Larsen
-- Script to modify
database owner, and remove all users that
-- are not mapped to
logins.
set nocount on
-- Section 1: Create
temporary table to hold databases to process
-- drop table if it
already exists
if (select
object_id('tempdb..##dbnames')) is not null
drop table ##dbnames
-- Create table to hold
databases to process
create table ##dbnames
(dbname varchar(128))
-- Section 2: Determine
what databases have orphan users
exec
master.dbo.sp_MSforeachdb 'insert into ##dbnames select ''?'' from
master..syslogins l right join ?..sysusers u
on l.sid = u.sid
where l.sid is null and
issqlrole <> 1 and isapprole <> 1
and (u.name <>
''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <>
''system_function_schema'')
having count(*) > 0'
-- Section 3: Create local
variables needed
declare @CNT int
declare @name char(128)
declare @sid varbinary(85)
declare @cmd nchar(4000)
declare @c int
declare @hexnum char(100)
declare @db varchar(100)
-- Section 5: Process
through each database and remove orphan users
select @cnt=count(*) from
##DBNAMES
While @CNT > 0
begin
-- get the name of the top
database
select top 1 @db=dbname from ##DBNAMES
-- delete top database
delete from ##DBNAMES where dbname = @db
-- Build and execute
command to determine if DBO is not mapped to login
set @cmd = 'select @cnt = count(*) from master..syslogins l
right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid' +
' where l.sid is null and u.name = ''DBO'''
exec sp_executesql @cmd,N'@cnt int out',@cnt out
-- if DB is not mapped to
login that exists map DBO to SA
if @cnt = 1
begin
print 'exec ' + @db + '..sp_changedbowner ''SA'''
-- exec sp_changedbowner 'SA'
end -- if @cnt = 1
-- drop table if it
already exists
if (select
object_id('tempdb..##orphans')) is not null
drop table ##orphans
-- Create table to hold
orphan users
create table ##orphans
(orphan varchar(128))
-- Build and execute
command to get list of all orphan users (Windows and SQL Server)
-- for current database
being processed
set @cmd = 'insert into ##orphans select u.name from master..syslogins
l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid ' +
'where l.sid is null and issqlrole <> 1 and
isapprole <> 1 ' +
'and (u.name <> ''INFORMATION_SCHEMA'' and
u.name <> ''guest'' ' +
'and u.name <> ''system_function_schema'')'
exec (@cmd)
-- Are there orphans
select @cnt = count(*) from ##orphans
WHILE @cnt > 0
BEGIN
-- get top orphan
select top 1 @name= orphan from ##orphans
-- delete top orphan
delete from ##orphans
where orphan = @name
-- Build command to drop
user from database.
set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' +
rtrim(@name) + ''''
print @cmd
--exec (@cmd)
-- are there orphans left
select @cnt = count(*) from ##orphans
end -- WHILE @cnt >
0
-- are the still databases
to process
select @cnt=count(*) from
##dbnames
end -- while @cnt > 0
-- Remove temporary tables
drop table ##dbnames,
##orphans
|
