Mirroring and logins

Hi,
I am maintaining a mirroring set up that I did not create. I have two
boxes with the same logins and 15-20 mirrored databases. My issue is
that I know some of the sids for SQL logins are different between the
boxes so that when we fail over in a crisis, some of the apps are not
going to be able to connect because of the ophaned login process. Is
there any way that I can run an automated process/script to find out
in advance what sids don't match. We have over 100 SQL logins that
exist on each server but may be mis-matched. I don't want to mirror
over and run the EXEC sp_change_users_login for each user on each
database.
Thanks in advance...Kristina
> over and run the EXEC sp_change_users_login for each user on each
> database.
If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...|||On Apr 16, 10:30=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina> over and run the EXEC sp_change_users_login for each user =A0on =each
> > database.
> If you have SP2 you ca use ALTER USER username WITH LOGIN =3D'login'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
> > Hi,
> > I am maintaining a mirroring set up that I did not create. I have two
> > boxes with the same logins and 15-20 mirrored databases. My issue is
> > that I know some of the sids for SQL logins are different between the
> > boxes so that when we fail over in a crisis, some of the apps are not
> > going to be able to connect because of the ophaned login process. Is
> > there any way that I can run an automated process/script to find out
> > in advance what sids don't match. We have over 100 SQL logins that
> > exist on each server but may be mis-matched. I don't want to mirror
> > over and run the EXEC sp_change_users_login for each user =A0on each
> > database.
> > Thanks in advance...- Hide quoted text -
> - Show quoted text -
I don't think that will do the trick. On a mirrored instance the
databases are in a restoring mode so we can't do that. The databases
are not accessible.|||Perhaps you can create a snapshot of your mirrored database? That should get you to
sys.database_principals (in the snapshot database) which you can check against
sys.server_principals.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...|||On Apr 16, 10:26=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Perhaps you can create a snapshot of your mirrored database? That should g=et you to
> sys.database_principals (in the snapshot database) which you can check aga=inst
> sys.server_principals.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
> > Hi,
> > I am maintaining a mirroring set up that I did not create. I have two
> > boxes with the same logins and 15-20 mirrored databases. My issue is
> > that I know some of the sids for SQL logins are different between the
> > boxes so that when we fail over in a crisis, some of the apps are not
> > going to be able to connect because of the ophaned login process. Is
> > there any way that I can run an automated process/script to find out
> > in advance what sids don't match. We have over 100 SQL logins that
> > exist on each server but may be mis-matched. I don't want to mirror
> > over and run the EXEC sp_change_users_login for each user =A0on each
> > database.
> > Thanks in advance...- Hide quoted text -
> - Show quoted text -
GREAT IDEA!!! Thanks! Now I know how to solve my problelm. I don't
know why I didn't think of this myself.