Home Forums Article Discussions Article Discussions by Author Discuss content posted by Prem Kumar Fix Orphan Users for All databases RE: Fix Orphan Users for All databases

  • April 19, 2017 at 12:55 pm

    #1938925

    Therese Hutcheson - Wednesday, April 19, 2017 11:41 AM

    Thank you for the information.  I like the code and am going to test, however when I run the EXEC sp_change_users_login 'Report' on a database with several users, connected to a login or not, it doesn't return any records.  I ran the code on master and several different databases (SQL 2014 and SQL 2016) all with Users.  This is so simple I can't imagine what I am missing but obviously I am .

    sp_change_users_login 'Report' will only report on orphaned users - one where the SID in the database's sys.users table doesn't match the SID in the server's master.sys.logins table.  (This is from memory - might not have the table names quite right here...)  See https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql for more.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com