Post reply

Can't access data from database restored from backup if not sysadmin

  • July 30, 2009 at 7:24 am

    #212755

    Hi everybody! I need some help please:

    - I'm creating a .bak file from a remote sql server 2005 express database.

    - I'm manually copying this file to another computer.

    - On this computer I open SQL Server Management Studio Express and connect to database A with user U that has public and db_creator roles. User U is also the db_owner of database A.

    - Using User U, I restore the .bak file to a new database B with the following T-SQL:

    RESTORE DATABASE DatabaseB

    FROM DISK = N'E:\Databases\DatabaseB_Backup.bak'

    WITHREPLACE,

    MOVE 'DatabaseB' TO N'E:\DatabaseB.mdf',

    MOVE 'DatabaseB_log' TO N'E:\DatabaseB.ldf'

    - So far so good.

    - Next I trying to select data from DatabaseB with either one of these:

    select * from DatabaseB..tableX

    --or

    select * from DatabaseB.dbo.tableX

    --or

    use DatabaseB

    select * from tableX

    - I get an error message saying:

    Msg 916, Level 14, State 1, Line 1

    The server principal "UserU" is not able to access the database "DatabaseB" under the current security context.

    I've look through the web and couldn't find any clue about how to resolve this. How come my user who restored the database doesn't have access to the data. It should since he`s the one who replicated it. Adding sysadmin role to my user solves the problem but that's not ideal for obvious reasons.

    Any help would be greatly appreciated.

    Thank you!

  • July 30, 2009 at 7:38 am

    #1032378

    So you backup a database from 'server a' and then move move the backup file to 'server b' and restore it.

    Unless you restore the system databases as well the logins from 'server a' do not follow to 'server b'. So you have users in your database after it is restored but the logins that are mapped to the user are lost. You will need to recreate these logins on 'server b' as they are on 'server a'.

    http://support.microsoft.com/kb/314546 -- check out step 2.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • July 30, 2009 at 8:45 am

    #1032428

    Even if you had the same user name on that new instance, the mapping to the database might not be correct as it's by SID, not name. You can use sp_change_users_login to find orphaned users and re-sync them.

  • July 31, 2009 at 5:53 am

    #1032871

    That was really helpful guys. Thanks a lot!

  • May 26, 2013 at 7:38 pm

    #1618652

    I also encounter this problem. However, I don't want to use sp_chang_users_login because this store procedure is for sysadmin users (ex: "sa",...). Can we solve this problem by User U?

    Thanks a lot!

  • May 28, 2013 at 7:14 am

    #1618940

    Until 2012, all of the previous versions of MS SQL kept the user info in the master DB. If you restore a DB to another server, those User IDs will not exist in the 'Restored to' master DB. (Even though the USERS are restored with the DB, they do not have a matching row in the master DB and will not work.) The SA user has to remove the old IDs and create new ones, before any user can access it.

  • June 17, 2015 at 4:33 am

    #1806009

    To avoid this issue add your user ( the user who is taking backup) as a user of the Backup database, and your user should have (login) access in the new instance.

  • December 18, 2015 at 8:12 am

    #1846789

    So after a restore do not restore the master.db just recreate the users manually?

    Thanks

    Chet

Viewing 8 posts - 1 through 7 (of 7 total)

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