Re: dbcreator and restore?

From: Ray Higdon (rayhigdon_at_higdonconsulting.com)
Date: 10/27/03 

Date: Mon, 27 Oct 2003 12:35:17 -0800

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

Relevant Pages

  • Re: dbcreator and restore?
    ... The issue here is that I want the user on the dev server ... not want that user to be dbo on the production server. ... login DBA_Group has dbcreator role ... DBA_Group will fail to restore the db .BAK. ...
    (microsoft.public.sqlserver.security)
  • Re: dbcreator and restore?
    ... It seems very strange that a user who is dbo cannot even do a backup. ... what exactly do you mean by making the login dbo? ... The output of sp_helpuser (while in the database) could be useful. ...
    (microsoft.public.sqlserver.security)
  • Re: ISV Using SQL Authentication - a login concern
    ... How you login depends on the tool you are using. ... you can backup a database with users in it. ... access to restore a database. ...
    (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)