locked
DB/JOB Owner under former Employee's Active Directory Account RRS feed

  • Question

  • We had a DBA that left the company over a year ago. He worked here for a long time. Some JOBS/DB are still under his AD account and we want to do a cleanup and shut down his AD account.

    What is the best practice? What pitfalls should I watch out for? Basically, I want to so re-assign all DB/JOB ownership from his account to "sa". 

    TIA


    Thursday, March 28, 2019 7:27 PM

Answers

  • Hi TIA,
    From your description, you can re-assign all DB/JOB ownership with below commands:  
    a. find out and re-assign the DB ownership

    SELECT [name], SUSER_SNAME(owner_sid) FROM sys.databases
    
    USE database name 
    EXEC sp_changedbowner 'sa'

    b. find out and re-assign the JOB ownership

    SELECT J.name AS Job_Name
    , L.name AS Job_Owner
    FROM msdb.dbo.sysjobs_view J
    INNER JOIN
    master.dbo.syslogins L
    ON J.owner_sid = L.sid
    
    EXEC MSDB.dbo.sp_update_job
    @job_name = 'Job_Name',
    @owner_login_name = 'sa'
    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, March 29, 2019 7:42 AM

All replies

  • Loop the following queries to find out the previous DBA's account and update it to sa. Since sa has the server sysadmin role, it should be okay.

    SELECT name AS DatabaseName, suser_sname(owner_sid) AS DatabaseOwner
    FROM master.sys.databases;
    SELECT name AS SqlJobName, suser_sname(owner_sid) AS SqlJobOwner
    FROM msdb.dbo.sysjobs;

    A Fan of SSIS, SSRS and SSAS

    Thursday, March 28, 2019 7:58 PM
  • Hi TIA,
    From your description, you can re-assign all DB/JOB ownership with below commands:  
    a. find out and re-assign the DB ownership

    SELECT [name], SUSER_SNAME(owner_sid) FROM sys.databases
    
    USE database name 
    EXEC sp_changedbowner 'sa'

    b. find out and re-assign the JOB ownership

    SELECT J.name AS Job_Name
    , L.name AS Job_Owner
    FROM msdb.dbo.sysjobs_view J
    INNER JOIN
    master.dbo.syslogins L
    ON J.owner_sid = L.sid
    
    EXEC MSDB.dbo.sp_update_job
    @job_name = 'Job_Name',
    @owner_login_name = 'sa'
    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, March 29, 2019 7:42 AM
  • Before just changing the job owner to "sa" (as suggested), check of that person is a sysadmin in your SQL server. If he is a sysadmin, then just change to "sa", no changes in privileges. If not, then consider the security implications. See for instance: http://sqlblog.karaszi.com/sql-server-agent-jobs-and-user-contexts/

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, March 29, 2019 9:49 AM
  • The former responses are correct

    I would just like to add a word on best practices.

    For ownership of Databases it is recommended, to use a low privileged account. But this requires some planning. (Here are some ideas: SQL Server Database Ownership: survey results & recommendations). That is the reason why mostly sa is used. Which has the advantage that the account will not become orphaned like a real persons Login name.

    For jobs only the owner has permissions to edit the respective Job. Therefore sa is often a placeholder for "all DBAs with sysadmin privileges.

    If some non-sysadmin is supposed to update certain jobs, that would be a reason to change the owner to his account.

    hope this helps

    Andreas


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Monday, April 1, 2019 8:42 PM