Logins don''t work after restoring on a different server

  • I completed a backup of several SQL 2000 db and then completed the restores(with overwrite existing db option) on a different SQL 2000 server. That went off without a hitch. The only problem I have now is the logins dont work.

    I have tried running EXEC sp_change_users_login 'Report', and the logins appear. However, when I run EXEC sp_helplogins 'john', the results are empty.

    So, I am guessing all I need is a sp that will re-associate my logins with the correct db and grant the appropriate permissions.

    If anyone has any ideas that would be great.

    PS. Tried using sp_change_users with no luck (but it's probably me as I am new to DBA work).

  • Wish I could tell you where I acquired this script in order to give the genius acknowledgement...it may have been from this site.

    /*************************************************************************************

    This procedure should be created in the Master database. This procedure takes no

    parameters. It will remap orphaned users in the current database to EXISTING logins

    of the same name. This is usefull in the case a new database is created by restoring

    a backup to a new database, or by attaching the datafiles to a new server.

    *************************************************************************************/

    --sp_msforeachdb 'use ?;exec master..sp_fixusers'

    IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

    BEGIN

    DROP PROCEDURE dbo.sp_fixusers

    IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

    PRINT '<<>>'

    ELSE

    PRINT '<<>>'

    END

    GO

    CREATE PROCEDURE dbo.sp_fixusers

    AS

    BEGIN

    DECLARE @username varchar(25)

    DECLARE fixusers CURSOR

    FOR

    SELECT UserName = name FROM sysusers

    WHERE issqluser = 1 and (sid is not null and sid 0x0)

    and suser_sname(sid) is null

    ORDER BY name

    OPEN fixusers

    FETCH NEXT FROM fixusers

    INTO @username

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @username='dbo'

    BEGIN

    EXEC sp_changedbowner 'sa'

    END

    ELSE

    BEGIN

    EXEC sp_change_users_login 'update_one', @username, @username

    END

    FETCH NEXT FROM fixusers

    INTO @username

    END

    CLOSE fixusers

    DEALLOCATE fixusers

    END

    go

    IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

    PRINT '<<>>'

    ELSE

    PRINT '<<>>'

    go



    Michelle

  • It worked!

    Thanks so much for the help Michelle! 

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

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