Skip to content

Changing Job and Database Owners to SA

2012 August 6
by Shannon Lowder

I’m the Doctor

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!

3 Responses leave one →
  1. September 11, 2013

    This is great thank you!

    Have you experienced any environments that have farms/estates where the ownership change to a SQL account (SA) has caused issues?

    I am going through an estate of over a dozen or so instances, some of which are SharePoint. I will be reading more in to this in particular because the nature of those installations demands a domain account and is reflected in the database ownership.

    • September 24, 2013

      Changing the database owner shouldn’t affect permissions. If you have a database owned by a domain account, then than account goes away, you could find yourself in a predicament. I’ve scripted a change database owner to SA, and run it against whole servers before, after testing in dev to make sure no permissions were affected.

      Always test something before applying it across your enterprise. That’s just best practice!

      • September 24, 2013

        Yes of course testing in a dev environment is a must. Well for those who value their careers anyways 🙂

        All went well, but the SharePoint boxes were left. The cause of my work was to rectify the very predicament you mention. I was cleaning this up in as tidy a way as possible.

        Your script was used across 2 dozen instances of 2005/2008 r2 🙂

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS