Re: dbcreator and restore?

From: Linda (lritter_at_creativesolutions.com)
Date: 10/28/03

  • Next message: Sue Hoegemeier: "Re: How get WIN NT/2000 users full name?"
    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?
    >-----Original Message-----
    >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
    >http://support.microsoft.com/default.aspx?scid=kb;en-
    >us;246133
    >
    >HTH
    >
    >Ray Higdon MCSE, MCDBA, CCNA
    >
    >>-----Original Message-----
    >>It is working now. I have the login as dbo (not
    >db_owner
    >>role). I also created a backup after the login was set
    >to
    >>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
    >>error message.
    >>Does this make sense?
    >>
    >>I don't want this login to be dbo on production.
    >>
    >>Is there any way around this?
    >>>-----Original Message-----
    >>>Hi Linda
    >>>
    >>>It seems very strange that a user who is dbo cannot
    >even
    >>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
    >>db_owner role?
    >>>
    >>>The output of sp_helpuser (while in the database) could
    >>be useful.
    >>>
    >>>--
    >>>HTH
    >>>----------------
    >>>Kalen Delaney
    >>>SQL Server MVP
    >>>www.SolidQualityLearning.com
    >>>
    >>>
    >>>"Linda" <lritter@creativesolutions.com> wrote in message
    >>>news:070401c39c8f$bbfb70a0$a501280a@phx.gbl...
    >>>> I am looking for a way to allow a junior dba to
    >restore
    >>>> databases on development servers. As a test, I
    >created a
    >>>> new login called DBAGroup. I then assigned this login
    >>the
    >>>> dbcreator fixed server role. But the error I'm
    >>receiving
    >>>> 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
    >the
    >>>> same error.
    >>>>
    >>>> Is there any role or other combination of roles
    >(short
    >>of
    >>>> sa) that would allow backup and restore?
    >>>
    >>>
    >>>.
    >>>
    >>.
    >>
    >.
    >


  • Next message: Sue Hoegemeier: "Re: How get WIN NT/2000 users full name?"