Post reply

Backup/restore user ids loses rights

  • August 4, 2009 at 1:01 pm

    #207798

    Hi,

    We're using SQL 2005, the prod database gets backed up nightly, the backup file gets copied to the dev server and restored automatically. The problem is that the user ids created on prod lose their access right on the dev server.

    Any ideas?

    Aldo

  • August 4, 2009 at 1:32 pm

    #1034493

    yes, that will happen any time you are restoring from one server to another. It's all about the difference between logins and database users and thier various SIDS. If you using 2005 you can use the alter user command to correct the logins, if using 2000 read up on sp_change_users_login in BOL. Reading through the rest of the Logins and users topics on BOL wouldn't be a bad idea as well for some better background knowledge.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • August 4, 2009 at 1:44 pm

    #1034500

    use the following in a script so that this will fix once its restored

    sp_change_users_login 'report'

    sp_change_users_login 'Auto_Fix', 'usr', 'pwd'


    - Pradyothana     http://www.msqlserver.com

  • August 4, 2009 at 2:51 pm

    #1034532

    Use this query to fill all orphan users on any database.

    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

    EXEC sp_change_users_login 'update_one', @username, @username

    FETCH NEXT FROM fixusers

    INTO @username

    END CLOSE fixusers

    DEALLOCATE fixusers

    END

    SQL DBA.

  • August 5, 2009 at 5:43 pm

    #1035140

    Thank you so much!!!

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

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