Post reply

Should I delete Orphaned Users

  • June 29, 2017 at 2:35 pm

    #343358

    I have been tasked to remove all Orphaned users on all the databases in several instances on our production servers and wanted to ask how will I ensure that I do not break any application if i do so.

    I have the script to check for Orphaned users in every database and I generated a list for each instance.  I also have the script that will remove these orphaned users as well as I have the script to sync up the orphaned db users with the SQL Logins.   

    My plan is to
    A: Get the list of all orphaned users - Done
    B: Execute the script to sync the SQL logins and DB users - Planned
    C: Remove the oprhaned users in the database - Planned

    So my question is
    A: How can i know that it is safe to remove the orphaned users
    B: If Guest is disabled and these accounts are not part of any AD groups added as SQL Logins can they be used for accessing the databases

    My thoughts are to do the following
    A: check to see if these accounts are valid in Active Directory
    B: Search the error logs for evidence of them logging in (failure, success)
    C: Talk to the application owners to see if the Software is creating or using these accounts
    D: Check to see if these Orphaned users are part of any AD groups that are added as SQL Logins

    Any feedback is appreciated

    Jeff

  • June 29, 2017 at 3:51 pm

    #1948853

    I got tasked with a similar thing recently and something else to watch for is user that is created without login or user that is created with a login of a different name.
    If they are AD users, I'd hope that you would have a matching AD login.  If they are AD Group logins, I'd hope that you'd have a matching AD group user.

    Now that isn't always the case I know.  In my case, what I ended up dong was matching the user SID to the login SID (where possible), and when they didn't match up with any login SID, manually going through them to see if they sounded like they should be safe to remove.  Then script them all out for removal and script them all out for re-creation.
    And having a rollback script.
    I believe (but could be wrong) that if you have an orphaned user who is a member of an AD group that has a login, if the SIDs don't match, they are not actually associated with each other.  If the SID's don't match, then the login is not acutally associated with the user even though the user is part of the AD group.

    So, all you really need to watch for is the "user created without login".

    Also, when you go to drop the orphaned users, you will need to check if they own any schemas.  If they do, you will need to change ownership of the schema.  I believe this actually applies to all database objects.  If the user is an owner of an object and you try to drop the user, you should get an error.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • June 29, 2017 at 3:55 pm

    #1948854

    I don't know that you can be 100% certain. What if it's some login used once or twice a year for something? I would probably leave them for a bit in disabled status instead of just deleting them. Or if it's a windows group in question, deny status since you can't disable a group. And also check if any of them are sysadmins as you would need to disable those.
    I would think you would also want to script out the logins, their mappings, their permissions, roles, etc so that you can replace whatever you delete if needed. I would also want to check if any of these are actually users without logins rather than orphans.
    In terms of the other questions, it's hard to tell what type of logins these are. SQL logins usually refers to SQL Server authentication logins. Windows logins usually refers to Windows authentication. I would guess you are talking about windows accounts but you have a lot of mentions about about SQL logins mixed in with AD groups so maybe it's both. Nonetheless, if you have created a user without a login then yes that can be used to get into the database. And just that database.

    Sue

    EDIT: I'm too slow...BMG beat me to most of the points. At least we didn't contradict each other 🙂

  • June 29, 2017 at 8:32 pm

    #1948866

    Talking to your application owners is never a bad idea.  I'd start there.  I hope this isn't the case, but if anyone's using the sa login, get them to change as soon as you can.

    I'd do pretty much everything bmg002 and Sue listed.  Your rollback script should include creation of the logins, granting server privs, creation of the users and granting database privs.  Don't forget to include object privs as well as server and database roles.  I'd also save is somewhere safe for at least a year.

    If it were me, I'd leave them disabled for a month or so before dropping them.  If you don't receive any complaints, they're probably safe to drop.  You always have your rollback script, which you can run selectively depending on which logins and users are required.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 30, 2017 at 5:16 am

    #1948906

    not all orphaned users are actually orphaned users, they may be users created for execution\access purposes so check first

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • June 30, 2017 at 5:45 am

    #1948910

    I would be inclined to disable them for at least a month first.

    USE YourDB
    GO
    REVOKE CONNECT FROM "UserToDisable"
    GO

  • July 2, 2017 at 9:03 am

    #1949067

    Thank you very much for the feedback.  You have given me some good information and Ideas

    Jeff

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply