Script to Delete Orphan Users in the database

  • Hello Everyone

    I am looking for a script, better than the one that I have tried to create, that will drop all the orphan users that do not have a login. I have created a new Staging environment, performed a backup and restore of all the databases, there are hundreds, with hundreds of users in each. But now I am looking for a means to easily remove all the orphan users in each database.

    Thank you in advance

    Andrew SQLDBA

  • Hi Andrew,

    I did something some time ago to my old company, but is using Powershell. If you are interest, this is the link 🙂

    http://www.simple-talk.com/sql/database-administration/exceptional-powershell-dba-pt1---orphaned-users/

    $hell your Experience !!![/url]

  • Thank You, but Sorry

    The Upper Management said no using PS. They only want t-sql, so that anyone can come in behind me and be able to modify the code.

    Thanks anyway

    Andrew SQLDBA

  • Hi Andrew,

    I used a version of the following script to resync orphaned users. I made a couple of quick changes to it and came up with this:

    USE database_name --Change to active database name

    go

    DECLARE @UserName nvarchar(255)

    DECLARE orphanuser_cur cursor for

    SELECT UserName = name

    FROM sysusers

    WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null

    ORDER BY name

    OPEN orphanuser_cur

    FETCH NEXT FROM orphanuser_cur INTO @UserName

    WHILE (@@fetch_status = 0)

    BEGIN

    PRINT @UserName + ' user name being dropped'

    --DROP USER @UserName

    FETCH NEXT FROM orphanuser_cur INTO @UserName

    END

    CLOSE orphanuser_cur

    DEALLOCATE orphanuser_cur

    go

    Be sure to change the "USE..." to the correct db name. Notice that the "DROP USER..." is commented out. Be sure to test it first before uncommenting the "DROP" to make sure the users are the ones you want.

    Hope it helps,

    Hank

Viewing 4 posts - 1 through 3 (of 3 total)

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