Re: Another question about DB Mirroring

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Why would you need to automate this? You only restore the db once, so make sure you restore logged in with a login which is identical as on the source server. Possibly use sp_help_revlogin to create such an identical login before the restore (the SIDs match). If you did that, then all should match up after that...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Luca" <Luca@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:3186CD41-3D15-40EE-B4CC-D01483B9178C@xxxxxxxxxxxxxxxx
Hi Tibor,
I've already tried the sp_changedbowner and it works well.

If the failover takes place, the db change to the other server
automatically, at this time can I automatize the sp_changedbowner?? Otherwise
I need to do it manually increasing the downtime. Especially if it happens
during the night, if could be automatized would be nice !

I hope I'm clear with my question...

Thank you so much
Luca

"Tibor Karaszi" wrote:

Hmm, so the system tables in the master database say the owner is
login A and the user table in the database say the owner (dbo) is
login B... Is it possible to do ALTER AUTHORIZATION or
sp_changedbowner after the restore?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Luca" <Luca@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E3D5199-19C0-4619-BC93-1DBCEBD5A981@xxxxxxxxxxxxxxxx
> Many thanks Tibor. I've found the procedure and it works great.
>
> Regarding the mirroring I restore the db to the second server > using
> the "NO
> Recovery" option; but this erase my db owner adding my domain
> account (the
> user that make the restore) instead of SQL user.
>
> Is there a way to leave the DB owner as it was before the restore
> process ?
>
> Luca
>
> "Tibor Karaszi" wrote:
>
>> The key here is to already have the login at the other end, and >> for
>> SQL Server logins, with the same SID. Google for >> sp_help_revlogin
>> and
>> take it from there.
>>
>> -- >> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Luca" <Luca@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:CDA2BFE5-B172-4520-8BD3-385EE47E7DCE@xxxxxxxxxxxxxxxx
>> > hello,
>> > I already wrote last week about db mirroring.
>> > I've setup a mirroring lab with principal-mirror-witness SQL >> > 2005
>> > std.
>> > Everything seems to be fine; if I stop the mirror or the
>> > principal,
>> > the DB
>> > is online and ready to accept client connections.
>> >
>> > I've tried a fail over, all is ok expect that at the end I >> > have
>> > to
>> > launch
>> > the DB properties and insert manually the db owner (SQL user).
>> >
>> > Another thing: What is the procedure to copy all the users on >> > the
>> > principal
>> > Server to the mirror ?? In order to have the users already OK
>> > when
>> > the mirror
>> > take place.
>> >
>> > Is there a way to do that ?? If I use the "copy DB" it works >> > but
>> > you
>> > cannot
>> > mirror a DB after that.
>> >
>> > Thanks a lot
>> > Luca
>> >
>>
>>



.

Relevant Pages

  • 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: Script A Login
    ... exacutes a number of stored procedures against the database, ... Login and User ... I restore databases all the time and don't need to re-create logins or ... The user account should have been in the database ...
    (microsoft.public.sqlserver.security)
  • Re: Problem in Database Restore
    ... > but the link with the login and sysusers in the database has been broken. ... This is normal when restoring/attaching a database from another server. ... To restore the database with only dbcreator, the login either needs to be ... Restores are bit easier with sysadmin role membership since you don't have ...
    (microsoft.public.sqlserver.security)
  • Re: MS WIN SP2-Downloaded&unable to login?
    ... > previous restore date. ... > Microsoft has solved this technical problem. ... > this WIN SP2 rendered my network inoperable. ... By "login" do you mean to your User Account or login using your internet ...
    (microsoft.public.windowsxp.general)
  • Re: Another question about DB Mirroring
    ... when I do a restore automatically the db owner is ... Tibor Karaszi, SQL Server MVP ... if I stop the mirror or the ...
    (microsoft.public.sqlserver.setup)