Our staff login to SQL Server using NT Authentication. The logins have Security Administrator, Disk Administrator and Database Creator Server Roles.
Staff memebers create a new database with Enterprise Manager and are automatically the dbo of that database. They then need to restore the database (again using EM) from a backup file sent to us from various clients. Obviously their login/user will not exist in this external backup file we have been sent. When they restore the database (and they are large so this can take an hour), the restore is almost complete when it gives the error :
Server user 'BLAH' is not a valid user in database 'clientdb'.
RESTORE DATABASE is terminating abnormally
The user is no longer dbo or even part of the database they just created.
IS there any way for me to get around this error without making all our staff System Administrors ?
If you login(as Administrator) does it allow you to restore ?.
Also try to restore without creating the database first i.e straight way choose restore option when the "Restore Database" pops up put a new name (whatever you want) and continue with restore procedure. Let me know what happens.
I tried to simulate our situation no errors came up.
If I log in as System Administrator there are no problems, but I really don't want all our staff to have System Administrator rights on the SQL Server.
I tried as per your suggestion of NOT creating the database first. The restore chugs along till 99% complete then shows the error message
"Server user blah is not a valid user in database CLIENTSDB.
Restore database is terminating abnormally"
The only slight success I found was if I knew one of the users in the client database, and created a login with the same name, then logged in as this new login, I could restore the database. However, I would need to know an existing user.. which most of the time I dont.