2008删除没有数据库用户映射的登录及该登陆对应的作业

use NorthWind
--#temp是有数据库用户映射的登录
create table #temp(dbname varchar(100),loginname varchar(100))
declare @str varchar(max)
declare @dropstr varchar(5000)
declare @dropjob varchar(5000)
set @dropjob=''
set @dropstr=''
set @str=''
select @str=@str+'select a.name as dbname,b.name as loginname
from '+name+'.sys.database_principals a ,sys.server_principals b where a.sid=b.sid and b.name<>''sa'' union '
from sys.databases where database_id>4 and state=0
set @str=SUBSTRING(@str,1,LEN(@str)-5)
set @str='insert into #temp '+@str
exec(@str)
--#temp1的到没有数据库用户映射的登录
select name into #temp1 from sys.server_principals where type='S'
and is_disabled=0
and name not in
(
select name from sys.server_principals where principal_id in
(
select member_principal_id from sys.server_role_members where role_principal_id=3
)
)
and name not in
(select loginname from #temp)

select * from #temp1

select @dropstr=@dropstr+'drop login '+name+';
'
from #temp1

--得到没有对应数据库的作业名
select b.name,a.name from msdb.dbo.sysjobs b,sys.server_principals a where b.owner_sid=a.sid
and a.name in (select name from #temp1)

select @dropjob=@dropjob+'exec msdb.dbo.sp_delete_job @job_name='+''''+b.name+''';
' from msdb.dbo.sysjobs b,sys.server_principals a where b.owner_sid=a.sid
and a.name in (select name from #temp1)

print @dropjob
print @dropstr

drop table #temp
drop table #temp1