代碼: 選擇全部
declare @dbname nvarchar(200), @cmd2 nvarchar(2000)
declare c_db cursor for
select name from sysdatabases where dbid > 4
open c_db
fetch next from c_db into @dbname
while @@fetch_status=0
begin
print @dbname
set @cmd2='use '+@dbname + ' ' +
'
declare @name nvarchar(200), @cmd nvarchar(200)
declare c_user cursor for
select name from sysusers where altuid=0 and createdate > ''2001/1/1''
open c_user
fetch next from c_user into @name
while @@fetch_status=0
begin
print @name
exec sp_change_users_login ''auto_fix'', @name
fetch next from c_user into @name
end
close c_user
deallocate c_user
'
exec (@cmd2)
fetch next from c_db into @dbname
end
close c_db
deallocate c_db
修改成以下的會主動過瀘不除在的帳號...
代碼: 選擇全部
declare @dbname nvarchar(200), @cmd2 nvarchar(2000)
declare c_db cursor for
select name from sysdatabases where dbid > 4
open c_db
fetch next from c_db into @dbname
while @@fetch_status=0
begin
print @dbname
set @cmd2='use '+@dbname +
'
declare @name nvarchar(200), @cmd nvarchar(200)
declare c_user cursor for
select a.name from sysusers a, master..syslogins b where a.name = b.name and a.altuid=0 and a.createdate > ''2001/1/1''
open c_user
fetch next from c_user into @name
while @@fetch_status=0
begin
print @name
exec sp_change_users_login ''auto_fix'', @name
fetch next from c_user into @name
end
close c_user
deallocate c_user
'
select @cmd2
exec(@cmd2)
fetch next from c_db into @dbname
end
close c_db
deallocate c_db
在 sql 2005 中, 必需再調整一下, 因為原來的 altuid=0 已不足夠做為條件, 目前測試可用的查詢方式為:
select name from sysusers where issqluser=1 and name not in ('dbo', 'guest', 'INformATION_SCHEMA', 'sys')
如此修改程序為:
代碼: 選擇全部
declare @dbname nvarchar(200), @cmd2 nvarchar(2000)
declare c_db cursor for
select name from sysdatabases where dbid > 4
open c_db
fetch next from c_db into @dbname
while @@fetch_status=0
begin
print @dbname
set @cmd2='use '+@dbname + ' ' +
'
declare @name nvarchar(200), @cmd nvarchar(200)
declare c_user cursor for
select name from sysusers where issqluser=1 and name not in (''dbo'', ''guest'', ''INformATION_SCHEMA'', ''sys'')
open c_user
fetch next from c_user into @name
while @@fetch_status=0
begin
print @name
exec sp_change_users_login ''auto_fix'', @name
fetch next from c_user into @name
end
close c_user
deallocate c_user
'
exec (@cmd2)
fetch next from c_db into @dbname
end
close c_db
deallocate c_db