Re: Permissions on Restore

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 10:54:06 +0200

Next step I would try is to make certain the SID is the same on both servers for the logins. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@discussions.microsoft.com> wrote in message
news:683001c493d7$7beb0cb0$a501280a@phx.gbl...
> Unfortunately, the same error is still experienced.
>
> Processed 7360 pages for database 'TEST',
> file 'IPSHOW_Data' on file 1.
> Processed 1 pages for database 'TEST', file 'IPSHOW_Log'
> on file 1.
> Server: Msg 916, Level 14, State 1, Line 1
> Server user 'ALISON' is not a valid user in
> database 'TEST'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Any other ideas ?
>
> >-----Original Message-----
> >How about not doing the restore from EM, but use QA
> instead. And after the restore change the
> >database owner, using sp_changedbowner (to "sa", for
> instance).
> >
> >-- 
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Alison" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:545501c49140$121f9290$a301280a@phx.gbl...
> >> As we receive these databases from our clients, we
> don't
> >> know their logins or SIDs.  They will not provide us
> with
> >> any information other than the .bak file.
> >>
> >> Is there any way to go about this without knowing the
> >> SIDs ?  Or is System Administration rights to our staff
> >> members the only solution ?
> >>
> >>
> >> >-----Original Message-----
> >> >Most likely you are using SQL Server logins and they
> do
> >> not have the same SID numbers in the two
> >> >databases.
> >> >
> >> >So, in the dest server, the login create a database
> and
> >> is the owner of the database. Then RESTORE.
> >> >This will make the dbo the SID of the originating SQL
> >> Server. Then EM tries to connect to the
> >> >database at the dest server, but cannot do this
> because
> >> of mis-matched SID for the dbo.
> >> >
> >> >Best is if you can make sure the logins have the same
> >> SID, search KB for sp_help_revlogins. Also, to
> >> >understand the topic, read in Books Online about
> >> sp_change_users_login.
> >> >-- 
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> >"Alison" <anonymous@discussions.microsoft.com> wrote
> in
> >> message
> >> >news:48b401c490af$9c53a320$a501280a@phx.gbl...
> >> >> 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 ?
> >> >>
> >> >> Thanks,
> >> >> Alison
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • RE: Creating a DB User *not* on current Machine
    ... detach/attach the database to a new server. ... Server.When you move a database from one server that is running SQL Server ... the security identification numbers of the logins in the master ...
    (microsoft.public.sqlserver.security)
  • Re: fix logins
    ... it is used exactly for SQL Server logins. ... See Books Online, sp_addlogin, and you'll find a parameter for specifying the SID. ... password stored inside a database, ...
    (microsoft.public.sqlserver.server)
  • Re: Logins
    ... no databases are checked, ... On the Server Roles tab, ... That server was upgraded to SQL Server ... moved, I recreated the SQL Server logins we had added, making sure that the ...
    (microsoft.public.sqlserver.security)
  • Re: NT > 2003 domain name changes
    ... Can't use the DTS option as it doesn't retain the SID, ... -restore database to SQL2K ... - cut out logins that do not apply to the database at hand ... >> windows authentication over to a new sql server on a new ...
    (microsoft.public.sqlserver.security)
  • RE: Environment variables
    ... my $sid; ... Once connected normal database security takes over. ... servers, I probably have to log in to each server before connecting. ... > my $dbh; ...
    (perl.beginners)