Fixing Orphaned Users

When ever you refresh a database to new location you need to fix the orphaned users on that database.

Below is the script for fixing orphaned users. If the login exists in that instance, below script will automatically fixes the orphaned users, otherwise will provide you the list of orphaned users information:

Below script need to be run on the refreshed database:

USE [Refreshed database name]

SET NOCOUNT ON

DECLARE @User sysname

DECLARE @NameFound bit

DECLARE orphan_cursor CURSOR FOR

-- The follow select statement was lifted from sp_change_users_login

-- under the area where the REPORT parameter is passed in and then

-- modified.  It's purpose is to identify users in the database that

-- do not match to a corresponding login by sid and then identify

-- whether or not there is a orresponding login by name

        select

                        name as UserName,

                        CASE WHEN suser_sid(name) is not null

                                    THEN 1

                                    ELSE 0

                       END as NameFound

            from sysusers

        where issqluser = 1

            and (sid is not null and sid <> 0x0)

            and suser_sname(sid) is null

        order by name

OPEN orphan_cursor

FETCH NEXT FROM orphan_cursor into @User, @NameFound

WHILE @@FETCH_STATUS = 0

BEGIN

            if @NameFound = 1

            begin

                        select 'Synchronizing user '+@User+' with login '+@User

                        exec sp_change_users_login 'UPDATE_ONE', @User, @User

            end

            else

            begin

                        select 'No matching login found for user '+@User

            end

            FETCH NEXT FROM orphan_cursor into @User, @NameFound

END

CLOSE orphan_cursor

DEALLOCATE orphan_cursor