SQL script to find database owners using T-SQL and create SQL Script to change it to 'sa'

Rédigé par M Sozezzo - - Aucun commentaire

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);

Fil RSS des articles de ce mot clé