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:30Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina> over and run the EXEC sp_change_users_login for each user Xon each
> If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
>
>
> - 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:26Xpm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> 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 MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
>
>
> - 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.