вторник, 20 сентября 2022 г.

Bulk change ownership in MSSQL

For Jobs

USE msdb ;  
GO  
EXEC dbo.sp_manage_jobs_by_login  
    @action = N'REASSIGN',  
    @current_owner_login_name = N'old_account',  
    @new_owner_login_name = N'new_account' ;  
GO  


For Databases

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'')
BEGIN USE [?]
    exec sp_changedbowner ''sa'' 
END' 

EXEC sp_MSforeachdb @command