Re: Permissions on Restore

From: Alison (anonymous_at_discussions.microsoft.com)
Date: 09/02/04 

Date: Thu, 2 Sep 2004 15:56:24 -0700

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: 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)
  • Re: transfering logins
    ... has their name, password, and a SID, among other attributes. ... In <your database>.dbo.sysusers, you will notice that each database user has ... database not a server object), but since the SIDs are generated at random, ... do not have this problem with Windows logins (hint: ...
    (microsoft.public.sqlserver.security)
Loading