Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't access data from database restored from backup if not sysadmin Expand / Collapse
Author
Message
Posted Thursday, July 30, 2009 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 2:26 PM
Points: 3, 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!
Post #762389
Posted Thursday, July 30, 2009 7:38 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 794, Visits: 2,079
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).TwitterUri
@wshawnmelton
PS C:\>(Find-Me).BlogUri
meltondba.wordpress.com
Post #762401
Posted Thursday, July 30, 2009 8:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Post #762462
Posted Friday, July 31, 2009 5:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 2:26 PM
Points: 3, Visits: 16
That was really helpful guys. Thanks a lot!
Post #763019
Posted Sunday, May 26, 2013 7:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 6:44 PM
Points: 46, 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!
Post #1456939
Posted Tuesday, May 28, 2013 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 3:50 AM
Points: 16, 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.


Post #1457303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse