sql server 如何查询出数据库作业所有者的信息并完成批量替换

今天数据库对入职一年的我建立了独立的数据库账号，之前我是和其他同事共享的，之前的所有者账号被废弃了，这时导致很多数据库作业执行失败。

下面是说明如何把之前所有的作业的所有者替换成正在使用的账号：

下面讲一下如何进行上面批量修改：

1、首先介绍几个所有者相关的表sys.syslogins每个登录帐户在表中对应一行；

直接查询这个表：

select * from msdb.dbo.syslogins  

select * from sys.database_principals 

可以查看都有哪些登录用户的被限制权限了

2、查询所有作业的表：sysjobs（可以去官网了解文档内容）

select * from msdb.dbo.sysjobs  

其中的sid和所有者表里的owner_sid是一致的

所以下面查询被废弃的所有者号下的所有作业

复制代码
--可以用这个先查一遍。
select a.*,b.name from
(
select * from msdb.dbo.sysjobs where owner_sid<>'0x01'
) a
left join master.sys.syslogins b on a.owner_sid=b.sid
where b.name like '%这里换成将要删除的帐号%'

下面是自动化更新所有涉及到的作业的所有者

declare @job_id varchar(300)
select a.job_id into #ttt from
(
select name,[enabled],owner_sid,job_id from msdb.dbo.sysjobs where owner_sid<>'0x01'
) a
left join master.sys.syslogins b on a.owner_sid=b.sid
where b.name is null or b.name like '%这里换成将要删除的帐号%'
while (select count(1) from #ttt)>0
begin
select top 1 @job_id=job_id from #ttt order by job_id asc
declare @sql varchar(500)
select @sql = 'EXEC msdb.dbo.sp_update_job @job_id=N'''+@job_id+''', @owner_login_name=N''sa'''
print (@sql)
exec (@sql)
delete from #ttt where job_id=@job_id
end
truncate table #ttt
drop table #ttt