Post reply

orphan users

  • September 17, 2008 at 9:56 am

    #198286

    Hi Guys,

    Can anyone tell me how to fix all the orphan users of a sql server 2005 at once.

    whenever i restore a database all the users need to be fix, is there a way i can do all at once.

    Thanks

  • September 17, 2008 at 10:00 am

    #873113

    Hi,

    Execute the following code prior to database restoration and execute its output once the database is restored:

    Orphan login fixation: DECLARE @user SYSNAME

    DECLARE @login SYSNAME

    DECLARE @SQL NVARCHAR(300)

    DECLARE cur_Users CURSOR FOR

    SELECT su.name,sl.loginname

    FROM sysusers su inner join master..syslogins sl

    ON sl.sid=su.sid

    AND su.NAME NOT IN ('guest', 'sys', 'INFORMATION_SCHEMA')

    ORDER BY su.name

    OPEN cur_Users

    FETCH NEXT

    FROM cur_Users INTO @user,@login

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @SQL = 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE'

    + '''' + ', ' + '''' + @user + '''' + ', ' + '''' + @login + ''''+char(10)+'GO'

    PRINT @SQL

    --EXEC sp_executesql @SQL

    FETCH NEXT

    FROM cur_Users INTO @user,@login

    END

    CLOSE cur_Users

    DEALLOCATE cur_Users

    HTH

    MJ

  • September 17, 2008 at 10:06 am

    #873119

    Thanks Manu....

  • September 18, 2008 at 1:22 pm

    #873882

    Hello,

    Their is simple way to do that stored procedure sp_change_users_login 'report' will give you all orphaned users from your server and if you want to fix particular user say 'user1' simply say:

    sp_change_users_login 'auto_fix','user1'.

    This one worked fine with me.

    Thanks,

    SC

  • September 18, 2008 at 1:25 pm

    #873886

    Hi,

    I am sorry its my bad,I didn't read ur full question.I thought you want to fix particular user later I realised it is for all users at one time.

    Regards,

    SC

  • September 18, 2008 at 3:32 pm

    #873945

    Thanks for you too SC

Viewing 6 posts - 1 through 5 (of 5 total)

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