locked
Remove an user account from all SQL Database servers RRS feed

  • Question

  • Hi All,

    One of our DBA is leaving the organization. We need to remove his ID from all SQL database servers and replace his ID with 'sa' If he is owner of any database or Jobs. We have CMS server. Please assist me in providing the script to achieve it. Thanks.


    Vinai Kumar Gandla

    Sunday, April 5, 2020 6:40 AM

Answers

  • Copy past the outpt of CommandToRun column and run in another query window

    ---database name

    select
    (case when D.is_read_only = 1 then '-- Remove ReadOnly State' when D.state_desc = 'ONLINE' then 'ALTER AUTHORIZATION on DATABASE::['+D.name+'] to [SA];' else '-- Turn On ' end) as CommandToRun
    ,D.name as Database_Name
    , D.database_id as Database_ID
    ,L.Name as Login_Name
    ,D.state_desc as Current_State
    ,D.is_read_only as [ReadOnly]
    from master.sys.databases D
    inner join master.sys.syslogins L on D.owner_sid = L.sid
    where L.Name = 'dba_loginame'
    order by D.Name;

    --Agent Jobs


    --Agent Jobs
    select
    J.name as SQL_Agent_Job_Name
    ,msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) as Job_Owner
    ,J.description
    ,C.name
    ,'EXEC msdb.dbo.sp_update_job @job_id=N'''+cast(job_id as varchar(150))+''', @owner_login_name=N''sa'' ' as RunCode
    from msdb.dbo.sysjobs j
    inner join master.sys.syslogins L on J.owner_sid = L.sid
    inner join msdb.dbo.syscategories C on C.category_id = J.category_id
    where msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) = 'dba_loginame';


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    • Edited by Uri DimantMVP Sunday, April 5, 2020 8:56 AM
    • Proposed as answer by pituachMVP Sunday, April 5, 2020 1:37 PM
    • Marked as answer by Vikki G Monday, April 6, 2020 5:30 PM
    Sunday, April 5, 2020 8:55 AM

All replies

  • Copy past the outpt of CommandToRun column and run in another query window

    ---database name

    select
    (case when D.is_read_only = 1 then '-- Remove ReadOnly State' when D.state_desc = 'ONLINE' then 'ALTER AUTHORIZATION on DATABASE::['+D.name+'] to [SA];' else '-- Turn On ' end) as CommandToRun
    ,D.name as Database_Name
    , D.database_id as Database_ID
    ,L.Name as Login_Name
    ,D.state_desc as Current_State
    ,D.is_read_only as [ReadOnly]
    from master.sys.databases D
    inner join master.sys.syslogins L on D.owner_sid = L.sid
    where L.Name = 'dba_loginame'
    order by D.Name;

    --Agent Jobs


    --Agent Jobs
    select
    J.name as SQL_Agent_Job_Name
    ,msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) as Job_Owner
    ,J.description
    ,C.name
    ,'EXEC msdb.dbo.sp_update_job @job_id=N'''+cast(job_id as varchar(150))+''', @owner_login_name=N''sa'' ' as RunCode
    from msdb.dbo.sysjobs j
    inner join master.sys.syslogins L on J.owner_sid = L.sid
    inner join msdb.dbo.syscategories C on C.category_id = J.category_id
    where msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) = 'dba_loginame';


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    • Edited by Uri DimantMVP Sunday, April 5, 2020 8:56 AM
    • Proposed as answer by pituachMVP Sunday, April 5, 2020 1:37 PM
    • Marked as answer by Vikki G Monday, April 6, 2020 5:30 PM
    Sunday, April 5, 2020 8:55 AM
  • Hi All,

    One of our DBA is leaving the organization. We need to remove his ID from all SQL database servers and replace his ID with 'sa' If he is owner of any database or Jobs. We have CMS server. Please assist me in providing the script to achieve it. Thanks.


    Vinai Kumar Gandla

    Good day Vikki,

    Start with changing the LOGIN password

    Next disable the LOGIN

    This will make sure that your retired DBA cannot login using this LOGIN or use any USER which is based on this LOGIN.

    Now that you have time... Next step you can start clean the entities from the server and the databases, which are related to that LOGIN.

    Here you can go to Uri's solution for example to change the owners of the databases and the JOBs. Next remove USERs which are based on that LOGIN.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, April 5, 2020 1:37 PM
  • Start with changing the LOGIN password

    Next disable the LOGIN

    This will make sure that your retired DBA cannot login using this LOGIN or use any USER which is based on this LOGIN.

    Yes, if it is an SQL login. I would guess that it is a Windows login, and in that case the eradication is a single strike in the AD.

    If there are SQL logins - we don't want to talk about many servers that may be affected....

    But, yes, the Windows login should be dropped from the server, since it is not good to have an orphaned login which is a sysadmin owner. (Then again, in a perfect world, the DBA had his sysadmin powers through membership in an AD group, and there are no logins to drop, but don't count on that.)

    When it comes to database users, there may also be reason for a cleanup, but assuming that the person was sysadmin on most servers, there will not be very many, as he/she mapped to dbo in all databases.

    The most critical thing to fix is job ownership should absolutely be changed. They will not run as long the owner is not changed. What is worse, CU updates may fail because of imperfections in Microsoft's upgrade scripts.

    I'm sorry, but I don't have anything canned for this situation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, April 5, 2020 3:28 PM
  • Hello Vikki G,

    Please also note that when you drop the use login, you may have left a few things inside your databases. If there were mappings between this login and one or more database users (one per database), these users are still there. You should drop all its dependencies as well in case of kind of issue.

    More detail about good practice on drop sql login, you could read and follow this tutorial: How to drop a SQL Server Login and all its dependencies

    Hope it will help.

    Best Regards

    Dawn


    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.

    Monday, April 6, 2020 2:54 AM
  • Thanks for the Scripts..

    Vinai Kumar Gandla

    Monday, April 6, 2020 5:30 PM
  • Thanks.

    Vinai Kumar Gandla

    Monday, April 6, 2020 5:31 PM
  • Hi Dimant,

    Scripts are executing successfully, but the output is empty for both the script. Could you please help to check on this.


    Vinai Kumar Gandla

    Thursday, April 9, 2020 12:18 PM