Re: dbcreator and restore?
From: Linda (lritter_at_creativesolutions.com)
Date: Tue, 28 Oct 2003 08:19:47 -0800
Not really. I routinely move users around and have to
adjust logins/users for the different spids.
The issue here is that I want the user on the dev server
to have the right to restore a particular database. I do
not want that user to be dbo on the production server.
How can we get around what appears to be a requirement for
the dbcreator role to be dbo on the db that is being
restored from. e.g.
PROD server db: dbo is the sa account
DEV server : login DBA_Group has dbcreator role
Suppose I make a backup of the PROD server db. and copy
the .BAK down to the DEV server. At this point, login
DBA_Group will fail to restore the db .BAK.
I complete the restore on the DEV server using my sa
login, and THEN do a sp_changedbowner to the login
DBA_Group, and do a backup of that db . THEN I do a
restore of the db using the login DBA_Group, it is
successful. (Because now the backup .BAK file says that
DBA_Group is the dbo?).
Does this make sense?
Is there a work around? Or something I'm missing?
>Because you are restoring a database with users in it
>that do not match logins on your machine.
>You might take a look at this URL
>Ray Higdon MCSE, MCDBA, CCNA
>>It is working now. I have the login as dbo (not
>>role). I also created a backup after the login was set
>>dbo and that is the backup that i successfully restored.
>>However, if I create a backup from production (where the
>>login is NOT dbo) and I use that as the backup.BAK from
>>which to restore onto the DEV server, then I get that
>>Does this make sense?
>>I don't want this login to be dbo on production.
>>Is there any way around this?
>>>It seems very strange that a user who is dbo cannot
>>do a backup. Can
>>>you verify exactly what steps you are performing, first
>>to make this login
>>>dbo, and then to perform the backup.
>>>Also, what exactly do you mean by making the login dbo?
>>Are they actually
>>>the owner of the database or are they only in the
>>>The output of sp_helpuser (while in the database) could
>>>SQL Server MVP
>>>"Linda" <email@example.com> wrote in message
>>>> I am looking for a way to allow a junior dba to
>>>> databases on development servers. As a test, I
>>>> new login called DBAGroup. I then assigned this login
>>>> dbcreator fixed server role. But the error I'm
>>>> when attempting to either restore or backup is:
>>>> Server user 'DBAGroup' is not a valid user in database
>>>> DBA_Test. BACKUP DATABASE is terminating abnormally.
>>>> I've tried making this login dbo in this db, but get
>>>> same error.
>>>> Is there any role or other combination of roles
>>>> sa) that would allow backup and restore?