Microsoft provides system stored procedures (dbo.sp_changedbowner) for changing the db owner.
Keep in mind, a user database should have a db owner associated with it; dont mis interpret this witj db_owner role.

I personally prefer setting the db owner to ‘sa’

SELECT NAME,
    suser_sname(owner_sid) AS 'owner',
    CASE
        WHEN suser_sname(owner_sid) = 'sa'
            THEN ''
        ELSE 'USE [' + NAME + ']; EXEC dbo.sp_changedbowner @loginame = N''sa'';'
        END ScriptSql
FROM sys.databases

We can fix owner's jobs with this script:


SELECT
      s.name
    , l.name
    , s.[enabled]
    , CASE WHEN l.name <> 'sa' OR l.name IS NULL
      THEN 'EXEC msdb.dbo.sp_update_job @job_id=N'''+CAST(job_id AS nvarchar(MAX))+''', @owner_login_name=N''sa'';'
      ELSE '' END AS ScriptSql
FROM      msdb..sysjobs        s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid;

Script to fix DB/Job owner to 'SA' without question.

PRINT '-- SQL Script to fix DB owner to SA'
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(max);
SET @sql = '';
SELECT @sql = @sql + 'USE [' + sys.databases.NAME + ']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;' + CHAR(13) + CHAR(10)
FROM            sys.databases
LEFT OUTER JOIN sys.syslogins ON sys.databases.owner_sid = sys.syslogins.sid
WHERE (sys.syslogins.NAME <> N'sa')
    OR (sys.syslogins.NAME IS NULL)
PRINT @sql;
EXEC (@sql);
PRINT '-- SQL Script to fix Job owner to SA'
SET @sql = '';
SELECT @sql = @sql + CASE WHEN l.NAME <> 'sa'
    OR l.NAME IS NULL     THEN 'EXEC msdb.dbo.sp_update_job @job_id=N''' + CAST(job_id AS NVARCHAR(MAX)) + ''', @owner_login_name=N''sa'';' + CHAR(13) + CHAR(10)
                          ELSE '' END
FROM      msdb..sysjobs        s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid;
PRINT @sql;
EXEC (@sql);

Script to fix database for all online databases.

Rédigé par Sozezzo - 15 septembre 2015 - Aucun commentaire

Set database owner = 'sa'

Set RECOVERY = Simple & Shrink databases


 

print '-- SQL Script to fix owner and Recouvery mode'
print ''
SET NOCOUNT ON;
Declare @sql nvarchar(max);
declare @dbname nvarchar(255);
BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db
FROM sys.databases f
WHERE f.state_desc = 'ONLINE'
    AND (f.database_id > 4)


while ((select count(*) from #db)>0)
BEGIN
    
    
    Set @sql = '';
    SELECT top 1
    @dbname = name,
     @sql =
    
     -- change db owner to SA
     + case when suser_sname(f.owner_sid) = 'sa' THEN '' ELSE 'USE ['+ f.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;' + CHAR(13) + CHAR(10)  END

     -- SET RECOVERY SIMPLE
     + CASE when f.recovery_model_desc = 'SIMPLE' THEN ''  ELSE +'USE [MASTER]; '+ 'ALTER DATABASE ['+ f.name+'] SET RECOVERY SIMPLE WITH NO_WAIT;'+ CHAR(13) + CHAR(10)
     +'USE [MASTER]; '+'DBCC SHRINKDATABASE(N'''+ f.name+''');' + CHAR(13) + CHAR(10) END
    FROM #db f
    ORDER BY f.name;

    if NOT(@sql = '')
    SET @sql = 'PRINT ''Database:'+@dbname+''';' + CHAR(13) + CHAR(10)+@sql;

    print @sql;
    --exec (@sql);

    delete from #db where name = @dbname;

END

Create script to change owner


PRINT '---------------------------';
PRINT '-- SQL Script to fix owner to "sa"';
PRINT '-- '+CAST(getdate() as nvarchar(100));
PRINT '---------------------------';
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = ''sa'') AND [name] = ''?'' ))
BEGIN
    PRINT ''---- Fix owner user to "sa"''
    USE [?];EXEC dbo.sp_changedbowner @loginame = N''sa'',@map=false;
END;
'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to fix owner to "sa"'';';
PRINT 'PRINT ''----------------------'';';
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END

Sql Script to set recovery mode full.


PRINT '---------------------------';
PRINT '-- SQL Script to Set Recovery Mode FULL';
PRINT '-- '+CAST(getdate() as nvarchar(100));
PRINT '---------------------------';
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = ''FULL'') AND [name] = ''?'' ))
BEGIN
    PRINT ''---- Fix recovery mode full''
    USE Master;ALTER DATABASE [?] SET RECOVERY FULL WITH NO_WAIT;;
END;
'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to Set Recovery Mode FULL'';';
PRINT 'PRINT ''----------------------'';';
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END