Fix orphan users in SQL Server 2005

  • Hi all,

    Is there any ways to fix 200+ SQL orphan users and 100+ windows orphan users in a single go.

    Thanks in advance.

  • You should find the info you're looking for here:

    http://msdn.microsoft.com/en-us/library/ms175475.aspx

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • But I think this is one by one process to fix orphan users.

    Thanks

  • Well, first of all you have to decide what to do with your orphaned users. Fix? Delete?

    For windows logins, you probably want to fix.

    For SQL logins, you have to decide. Drop? Rebind?

    -- Gianluca Sartori

  • I have to fix both types (windows, sql) of orphan users.

    Thanks.

  • More information needed, please. Are all the users in the same database? Do all users have the same name as their respective logins?

    John

  • Hi John

    Yes all the users are in the same database. And all the users have same name as their respective logins.

    Thanks.

  • did you try

    Use Master

    EXEC sp_change_users_login 'Auto_fix', 'userid'

    Use USERDB

    EXEC sp_change_users_login 'Auto_fix', 'userid'

    This sometimes works for me after I've done a redirected restores to remap the users

  • Jpotucek (1/11/2012)


    did you try

    Use Master

    EXEC sp_change_users_login 'Auto_fix', 'userid'

    Use USERDB

    EXEC sp_change_users_login 'Auto_fix', 'userid'

    This sometimes works for me after I've done a redirected restores to remap the users

    Yup, Auto fix should do the trick.


    Sujeet Singh

  • First, don't use sp_change_users_login. It's deprecated. And it doesn't support Windows Logins, or SQL Logins (one or the other, I can't remember which).

    Second, since all the users are in the same database, and they have the same as their logins, it's a simple matter to generate the SQL to make the changes. Syntax and column names may not be exactly right, but this should give you the right idea.

    SELECT 'ALTER USER ' + l.name + ' WITH LOGIN = ' + u.name

    FROM MyDB.sys.database_principals u

    JOIN master.sys.server_principals l

    ON l.name = u.name

    John

Viewing 10 posts - 1 through 9 (of 9 total)

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