SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
dambuster
dambuster
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 16
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'
WITH REPLACE,
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!
Shawn Melton
Shawn Melton
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2393 Visits: 3513
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: Administrators
Points: 66074 Visits: 19120
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
dambuster
dambuster
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 16
That was really helpful guys. Thanks a lot!
goodtomorrow01
goodtomorrow01
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 72
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
montgomerybrothers
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 51
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
ranadip.dey
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 114
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
czurawski
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 0
So after a restore do not restore the master.db just recreate the users manually?
Thanks
Chet
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search