Wednesday 21 March 2018

Change and Rename SQL Server Agent Job Name and Owner Login Name



On many occasions you need to change the SQL Server Agent Job Name and Login Name in your environment. Below script will change the job name and set the login name to ‘sa’.


DECLARE @servername NVARCHAR(30)=@@SERVERNAME,
        @name VARCHAR(100),
        @jobname NVARCHAR(130),
              @script NVARCHAR(300)

SET NOCOUNT ON;

DECLARE Job_Cursor CURSOR FOR 
SELECT name FROM msdb..sysjobs 
WHERE name LIKE 'DB_Maintenance%' AND name NOT LIKE '%'+@servername+'%'
OPEN Job_Cursor; 
FETCH NEXT FROM Job_Cursor INTO @name
WHILE @@FETCH_STATUS =
   BEGIN 
   SET @jobname=@name+'_'+@servername
   SELECT @script='EXEC msdb..sp_update_job         @job_name='''+@name+''''+',@new_name='''+@jobname+''''+',@owner_login_name=''sa'''
   PRINT @script
   EXEC sp_executesql @script
   FETCH NEXT FROM Job_Cursor INTO @name; 
   END; 
CLOSE Job_Cursor; 
DEALLOCATE Job_Cursor; 
GO

Monday 19 March 2018

Fix the database owner of all online SQL Server databases to ‘sa’

Below script will change the ownership of all the online SQL server databases whose current owner is not equal to ‘sa’. In many occasions the databases restore is performed by the DBA level user and owner remains unchanged.  The best practice is to change owner of the all SQL Server databases to ‘sa’ account.  You can use alter authorization starting from SQL Server 2012, if you are on a previous version of SQL Server you can continue using sp_changedbowner.

DECLARE @DBName SYSNAME
DECLARE @SQL NVARCHAR(300)

DECLARE cur_FixDBOwner CURSOR FOR
SELECT d.[name]
FROM sys.databases d
WHERE d.name not in ('master', 'model', 'tempdb')
AND d.owner_sid <> 0x01 AND d.state_desc='ONLINE'

OPEN cur_FixDBOwner

FETCH NEXT FROM cur_FixDBOwner INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQL='ALTER AUTHORIZATION ON DATABASE::'+QUOTENAME(@DBName)+' TO [sa];'
PRINT (@SQL)
EXEC  (@SQL)

FETCH NEXT FROM cur_FixDBOwner INTO @DBName
END

CLOSE cur_FixDBOwner

DEALLOCATE cur_FixDBOwner