use [Master]
Set nocount on;
Declare @DBName varchar(20);
Declare @SQL Varchar(2000);
Declare @DropDomain Varchar(256) = 'LIKE_ME\'; --domain user
Declare MyCursor Cursor for
select RTRIM(name) as DatabaseName
from sys.databases
where database_id > 4
and state = 0
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS >= 0
Begin
set @SQL = 'use [' + @DBName + '];'
print(@SQL)
execute(@SQL)
Create TABLE ##DatabaseRoleMember(DBRole varchar(50),MemberName varchar(50),MemberSID varchar(2000));
Create TABLE ##DropUnuseMember(SQLScript Varchar(1000));
Set @SQL = 'use [' + @DBName + ']; Insert into ##DatabaseRoleMember execute sp_helprolemember; '
Print(@SQL)
Execute(@SQL)
set @SQL = 'use [' + @DBName + ']; Insert into ##DropUnuseMember select ''use [' + @DBName + ']; DROP USER ['' + MemberName + ''];'' from ##DatabaseRoleMember where Upper(LEFT(MemberName,8)) = '''+@DropDomain+''';'
Print(@SQL)
Execute(@SQL)
Drop TABLE ##DatabaseRoleMember;
Declare MyCursor2 Cursor for
Select * from ##DropUnuseMember
Open MyCursor2
Fetch next from MyCursor2 into @SQL
While @@FETCH_STATUS >= 0
Begin
Print(@SQL)
Execute(@SQL)
Fetch next from MyCursor2 into @SQL
End
Close MyCursor2
Deallocate MyCursor2
Drop TABLE ##DropUnuseMember
Fetch next from MyCursor into @DBName
End
Close MyCursor
Deallocate MyCursor
Set nocount off;
沒有留言:
張貼留言