CREATE SCHEMA [ToBeDeleted]
go
declare @name nvarchar(1000), @sql nvarchar(4000)
declare tmp_cur cursor for
SELECT u.name --u.*--, l.*
FROM sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name
open tmp_cur
Fetch next from tmp_cur into @name
while @@fetch_status = 0
begin
print @name
set @sql = ''
------------------------
--rename schem onwer for users
declare tmp_cur2 cursor for
SELECT 'ALTER SCHEMA [ToBeDeleted] TRANSFER [' + s.Name + '].' + o.Name
FROM sys.Objects o
INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
WHERE s.Name = @name
And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
open tmp_cur2
Fetch next from tmp_cur2 into @sql
while @@fetch_status = 0
begin
print @sql
exec sp_executesql @sql
Fetch next from tmp_cur2 into @sql
print '---'
end
close tmp_cur2
deallocate tmp_cur2
------------------------
--drop users and schema
set @sql = 'DROP SCHEMA ['+@name+']'
print @sql
exec sp_executesql @sql
set @sql = 'DROP USER ['+@name+']'
print @sql
exec sp_executesql @sql
------------------------
Fetch next from tmp_cur into @name
print '---'
end
close tmp_cur
deallocate tmp_cur
-------------------------------------------------------------------------
No comments:
Post a Comment