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'
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
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.