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?"

    Relevant Pages

    • Re: dbcreator and restore?
      ... I also created a backup after the login was set ... >dbo and that is the backup that i successfully restored. ... >which to restore onto the DEV server, ...
      (microsoft.public.sqlserver.security)
    • Re: Script A Login
      ... When you create a Windows Login it identifies that Login by using the windows account SID which is unique. ... So you can take a DB from any server and plop it down (restore it) on any other server and the two will always match up. ... When I restore a database, all of the database User Accounts and Database Roles are restored. ... When I restore a backup, are you saying the connection between the SQL Login in the server and the User Account in the database is broken? ...
      (microsoft.public.sqlserver.security)
    • Re: db_owner role in SQL 2k
      ... Try not to say "If sa is THE dbo", it's like saying "If I am the joe". ... might help to either say "If sa is the database owner" or, ... The login 'sa' could be listed in sysdatabases as the database ... FULL permission to do anything and everything. ...
      (microsoft.public.sqlserver.programming)
    • Re: Two DB Owners
      ... full permissions in all databases. ... Note that the user will still be known as the 'dbo' user as long as the ... I need to add the login 'BUILTIN\Administrators' ... > 'DYNGRP' and 'BFGROUP' roles of the Dynamics and DAVCatalog databases. ...
      (microsoft.public.sqlserver.security)
    • Re: How do I set the login name for a user?
      ... You are better to make a dbo roled account (database god?), although that is not my first recommendation for connections. ... name, key name, without login. ...
      (microsoft.public.dotnet.framework.adonet)