Post reply

Remove Orphan Users from DB / POSH DROP

  • February 13, 2012 at 6:32 am

    #268211

    Hi all,

    I regularly restore a DB from a live server to a dev one. When i do this. I then need to remove all the user accounts that are "orphaned" and add in the users that need access to it.

    I have scripted out the adding users. But failing on the removing.

    If i try sp_revokedbaccess

    I get an error saying, the account doesnt exist.

    Msg 15151, Level 16, State 1, Procedure sp_revokedbaccess, Line 51

    Cannot drop the user 'Domain\Account', because it does not exist or you do not have permission.

    I have SysAdmin rights. So guessings its due to be an orphan account and not existing?

    I also tried looking at Powershell. but in nievity cant work out how to use DROP!?

    If i connect directly to posh via right click users in SSMS and Powershell.

    and DIR | GM

    I see the method DROP but i cant work out the syntax that needed.. im sure its simple but escaping me at the moment.

    Any help greatly received.

    S

  • February 13, 2012 at 7:44 am

    #1446242

    I think you need to simply drop the users.

    something like this would generate the commands to either fix orphans or drop teh users, would this help?

    /*--Results:

    DROP USER [GMS];

    DROP USER [CCFD01\Jack.Campbell];

    */

    SELECT

    CASE

    WHEN sv.name IS NULL --No matching name on server, drop the user.

    THEN 'DROP USER ' + quotename(db.name)+ ';'

    WHEN db.sid <> sv.sid --orphaned user with mathcing name, fix the user:

    THEN 'ALTER USER ' + quotename(db.name) + ' WITH LOGIN = ' +sv.name + ';'

    ELSE ''

    END,

    * FROM sys.database_principals db

    left outer join sys.server_principals sv

    on db.name = sv.name

    WHERE db.type_desc IN('SQL_USER','WINDOWS_USER')

    AND db.name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 13, 2012 at 8:32 am

    #1446279

    When you restore a backed up database, the server logins do not get restored. Only the database users. What you are doing is trying to remove a login that does not exist. Lowell's script should drop the users from the database.

    Jared
    CE - Microsoft

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

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