One of the services we’re trying to flesh out a bit more over at Hive Mind is our standard health check and resolution script.  Every DBA has his or her own set of scripts they carry around, either in a file, or a link to where to find it online.  Merging the best of all these scripts is difficult when you consider all the great resources within the SQL community.  One of the tasks addressed during a SQL Server health check is ownership of objects.

Whenever you have Jobs owned by users you run the risk of that account being removed, disabled, or corrupted in some way.  When that happens, your job is going to start failing you.  In a similar vein, database ownership gives owner a great deal of permission.  Probably more than is really needed to get the job done.  We can address both of these issues with some simple scripting.

In general, I’ll change the job owners to SA without too much worrying.  I still generate a rollback script and hold onto it until well after the change, just in case.  Database ownership is a bit touchier.  Users could have built code that could break if you suddenly remove ownership, and don’t give the affected users explicit permission to do their job!

My suggestion, work with your developers to determine what the minimum level of permission needed (for both the user and any code built and running against the database).  Generate scripts to grant those permissions.  Apply those first, then once you’ve worked out all the bugs in the permission change, change database owners to SA.

Now for the scripts!

Changing Job Owners

/******************************************************************************
Description:   
        Script that change the Jobs Owner to SA.  This Script will
        identify the non SA jobs, then create a rollback and a roll forward script
        that will change their owner as SA
Compatibility  :     2005 +
Input:
        Folder where rollback and roll forward scripts can be stored
*******************************************************************************/

--internal variables
DECLARE @ScriptList TABLE ( ScriptType VARCHAR(20), Script VARCHAR(MAX))

--rollback scripts
INSERT INTO @ScriptList
SELECT
          'rollback' as ScriptType
        , '-- Revert job: [' + j.name + '] to owned by: ['+ sl.name  + ']' + CHAR(13) + CHAR(10)
        + 'EXEC msdb..sp_update_job @job_id = ''' + CONVERT(VARCHAR(36), j.job_id) + ''''
        + ', @owner_login_name = ''' + sl.name + '''' as Script
FROM msdb.dbo.sysjobs j 
LEFT OUTER JOIN sys.syslogins sl
        ON j.owner_sid = sl.sid 
WHERE j.enabled = 1  AND sl.name <> SUSER_SNAME(0x01);

INSERT INTO @ScriptList
SELECT
          'rollforward' as ScriptType
        , 'EXEC msdb..sp_update_job @job_id = ''' + CONVERT(VARCHAR(36), j.job_id) + ''''
        + ', @owner_login_name = ''SA''' as Script
FROM msdb.dbo.sysjobs j 
LEFT OUTER JOIN sys.syslogins sl
        ON j.owner_sid = sl.sid 
WHERE j.enabled = 1  AND sl.name <> SUSER_SNAME(0x01);

--you can either BCP the following results to a rollback file
--or you can run the script in SSMS with ctrl+shift+F and save the results to a file
SELECT * FROM @scriptlist WHERE ScriptType = 'rollback';

--Once you've saved your rollback script.  Save your roll-forward script
SELECT * FROM @scriptlist WHERE ScriptType = 'rollforward';

Changing Database Owners

/******************************************************************************
Description:   
        Script that change the Database Owner to SA.  This Script will
        identify the non SA owned databases, then create a rollback and a roll forward script
        that will change their owner as SA
Compatibility  :     2005 +
Input:
        Folder where rollback and roll forward scripts can be stored
*******************************************************************************/

--internal variables
DECLARE @ScriptList TABLE ( ScriptType VARCHAR(20), Script VARCHAR(MAX))

INSERT INTO @ScriptList
SELECT
          'rollback' as ScriptType
        , '-- Revert database: [' + d.name + '] to owned by: ['+ SUSER_SNAME(owner_sid)  + ']' + CHAR(13) + CHAR(10)
        + 'ALTER AUTHORIZATION ON DATABASE::' + d.name + ' to [' + SUSER_SNAME(owner_sid) + '];'
FROM sys.databases  d
WHERE
        SUSER_SNAME(owner_sid) <> SUSER_SNAME(0x01);    
INSERT INTO @ScriptList
SELECT
          'rollforward' as ScriptType
        , 'ALTER AUTHORIZATION ON DATABASE::' + d.name + ' to SA;'
FROM sys.databases  d
WHERE
        SUSER_SNAME(owner_sid) <> SUSER_SNAME(0x01);  

--you can either BCP the following results to a rollback file
--or you can run the script in SSMS with ctrl+shift+F and save the results to a file
SELECT * FROM @scriptlist WHERE ScriptType = 'rollback';

--Once you've saved your rollback script.  Save your roll-forward script
SELECT * FROM @scriptlist WHERE ScriptType = 'rollforward';

Conclusion

One of the things we’re looking to do with this script is bundle it up with either PowerShell, SQLDiag, or some other tool, so we can simply deploy this script against a new server, and collect all these scripts automatically, with very little fuss or muss.

Do you have any suggestions for improvements to this script? Do you have a standard script you’d like to share? If so, let us know!