取数据库用户映射的脚本
作者：[51ak ]
create table #wkf_dbusename
(servername varchar(200) null,dbname varchar(50) NULL, usename varchar(1000) NULL);
declare @dbname varchar(50),@str varchar(500),@usename varchar(500),@table varchar(50),@allusename varchar(1000)
declare mycursor cursor for Ｓelect name from master.dbo.sysdatabases
where HAS_DBACCESS(name)=1 and DatabasePropertyEx(name,'Updateability')='READ_WRITE' and name not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB','NorthWind','distribution','AdventureWorks','AdventureWorksDW')
order by name
open mycursor
fetch mycursor into @dbname
while @@FETCH_STATUS=0
begin
Ｓelect @allusename=''
Ｓelect @table='['+@dbname+'].dbo.sysusers'
Ｓelect @str='insert into #wkf_dbusename
Ｓelect @@SERVERNAME,'''+@dbname+''',name from '+@table+'
where (isntuser=1 or issqluser=1) and name in(Ｓelect name from master.sys.syslogins) and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')'
exec (@str)
fetch mycursor into @dbname
end
close mycursor
deallocate mycursor
Ｓelect * from #wkf_dbusename