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

  • dambuster

    Old Hand

    Points: 337

    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:


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


    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


    select * from DatabaseB.dbo.tableX


    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!

  • Shawn Melton


    Points: 24675

    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'. -- check out step 2.

    Shawn Melton
    Twitter: @wsmelton
    Github: wsmelton

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

    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.

  • dambuster

    Old Hand

    Points: 337

    That was really helpful guys. Thanks a lot!

  • goodtomorrow01

    SSC Veteran

    Points: 252

    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!

  • montgomerybrothers

    Say Hey Kid

    Points: 674

    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.

  • ranadip.dey

    Old Hand

    Points: 376

    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.

  • czurawski

    Valued Member

    Points: 65

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



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

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