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’
1
2SELECT NAME,
3 suser_sname(owner_sid) AS 'owner',
4 CASE
5 WHEN suser_sname(owner_sid) = 'sa'
6 THEN ''
7 ELSE 'USE [' + NAME + ']; EXEC dbo.sp_changedbowner @loginame = N''sa'';'
8 END ScriptSql
9FROM sys.databases
We can fix owner’s jobs with this script:
1
2SELECT
3 s.name
4 , l.name
5 , s.[enabled]
6 , CASE WHEN l.name <> 'sa' OR l.name IS NULL
7 THEN 'EXEC msdb.dbo.sp_update_job @job_id=N'''+CAST(job_id AS nvarchar(MAX))+''', @owner_login_name=N''sa'';'
8 ELSE '' END AS ScriptSql
9FROM msdb..sysjobs s
10LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid;
Script to fix DB/Job owner to ‘SA’ without question.
1
2PRINT '-- SQL Script to fix DB owner to SA'
3SET NOCOUNT ON;
4DECLARE @sql NVARCHAR(max);
5SET @sql = '';
6SELECT @sql = @sql + 'USE [' + sys.databases.NAME + ']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;' + CHAR(13) + CHAR(10)
7FROM sys.databases
8LEFT OUTER JOIN sys.syslogins ON sys.databases.owner_sid = sys.syslogins.sid
9WHERE (sys.syslogins.NAME <> N'sa')
10 OR (sys.syslogins.NAME IS NULL)
11PRINT @sql;
12EXEC (@sql);
13PRINT '-- SQL Script to fix Job owner to SA'
14SET @sql = '';
15SELECT @sql = @sql + CASE WHEN l.NAME <> 'sa'
16 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)
17 ELSE '' END
18FROM msdb..sysjobs s
19LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid;
20PRINT @sql;
21EXEC (@sql);
Comments