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.

Minimum level of rights for a SQL Server DBA.

Previously, DBAs in our company used to have local machine administrator
access on the SQL Server boxes. As part of tightening server security, those
rights have been taken away from DBAs (in some cases they have been added to
'Power Users' group).
In order to install SQL Server, the account need to be a local machine
administrator (that's given otherwise installation gives error).
But, what type of minimal rights should be given to DBAs on the server
resources to let that person function properly?
When I am talking about resources, I mean rights to write to specific
directories like
* SQL Server programs/tools
* Common DLLs (in C:\Program files\common files...)
* Local backup directories
* Directory for snapshot/transactional replication transfer data.
And right to execute programs/utilities on the server like
* Perfmon (for system tuning/performance monitoring)
* Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
dependencies).
Is there anything that is not needed here or, alternatively, is there
anything that I missed?
Another dimension of this issue is the OS login access that is needed to run
the SQL Server services (needed for remote backups and replication).
Any help will be greatly appreciated!
Regards,
MZeeshan
At my last company, I didnt have admin rights... just SA. 90% of the time
it was fine. The other 10% it sucked. Common things I needed someone to hold
my hand on were:
1. Service restarts.
2. Hotfix/ service packs.
3. Set up stuff like Log Shipping where directory access is needed.
4. Wanting to just see how much disk space I had left on my backup drive.
5. Replication Snapshot.
I know you already mentioned alot of these. The problem is that when the DBA
needs these things, alot of time he needs them NOW. Not once he can have
someone come to his desk and log in as Admin. But like I said, 90% of the
time it was fine and I actually would prefer it. SA is usually good enough.
Any less than SA and a DBA cant get his work done.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> Previously, DBAs in our company used to have local machine administrator
> access on the SQL Server boxes. As part of tightening server security,
> those
> rights have been taken away from DBAs (in some cases they have been added
> to
> 'Power Users' group).
> In order to install SQL Server, the account need to be a local machine
> administrator (that's given otherwise installation gives error).
> But, what type of minimal rights should be given to DBAs on the server
> resources to let that person function properly?
> When I am talking about resources, I mean rights to write to specific
> directories like
> * SQL Server programs/tools
> * Common DLLs (in C:\Program files\common files...)
> * Local backup directories
> * Directory for snapshot/transactional replication transfer data.
> And right to execute programs/utilities on the server like
> * Perfmon (for system tuning/performance monitoring)
> * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> dependencies).
> Is there anything that is not needed here or, alternatively, is there
> anything that I missed?
> Another dimension of this issue is the OS login access that is needed to
> run
> the SQL Server services (needed for remote backups and replication).
> Any help will be greatly appreciated!
> --
> Regards,
> MZeeshan
>
|||Thanks!
Anyone? any other ideas?
Regards,
MZeeshan
"ChrisR" wrote:

> At my last company, I didnt have admin rights... just SA. 90% of the time
> it was fine. The other 10% it sucked. Common things I needed someone to hold
> my hand on were:
> 1. Service restarts.
> 2. Hotfix/ service packs.
> 3. Set up stuff like Log Shipping where directory access is needed.
> 4. Wanting to just see how much disk space I had left on my backup drive.
> 5. Replication Snapshot.
> I know you already mentioned alot of these. The problem is that when the DBA
> needs these things, alot of time he needs them NOW. Not once he can have
> someone come to his desk and log in as Admin. But like I said, 90% of the
> time it was fine and I actually would prefer it. SA is usually good enough.
> Any less than SA and a DBA cant get his work done.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
>
>
|||Hi MZeeshan,
If you need OS login access that is needed to run the SQL Server services
(needed for remote backups and replication). I think give DBA local
administrator privilege is necessary.
BTW, you are recommanded using the tools below to ensure the security of
your product server.
Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/d...=en&familyid=B
352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Minimum level of rights for a SQL Server DBA.

Previously, DBAs in our company used to have local machine administrator
access on the SQL Server boxes. As part of tightening server security, those
rights have been taken away from DBAs (in some cases they have been added to
'Power Users' group).
In order to install SQL Server, the account need to be a local machine
administrator (that's given otherwise installation gives error).
But, what type of minimal rights should be given to DBAs on the server
resources to let that person function properly?
When I am talking about resources, I mean rights to write to specific
directories like
* SQL Server programs/tools
* Common DLLs (in C:\Program files\common files...)
* Local backup directories
* Directory for snapshot/transactional replication transfer data.
And right to execute programs/utilities on the server like
* Perfmon (for system tuning/performance monitoring)
* Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
dependencies).
Is there anything that is not needed here or, alternatively, is there
anything that I missed?
Another dimension of this issue is the OS login access that is needed to run
the SQL Server services (needed for remote backups and replication).
Any help will be greatly appreciated!
--
Regards,
MZeeshanAt my last company, I didnt have admin rights... just SA. 90% of the time
it was fine. The other 10% it sucked. Common things I needed someone to hold
my hand on were:
1. Service restarts.
2. Hotfix/ service packs.
3. Set up stuff like Log Shipping where directory access is needed.
4. Wanting to just see how much disk space I had left on my backup drive.
5. Replication Snapshot.
I know you already mentioned alot of these. The problem is that when the DBA
needs these things, alot of time he needs them NOW. Not once he can have
someone come to his desk and log in as Admin. But like I said, 90% of the
time it was fine and I actually would prefer it. SA is usually good enough.
Any less than SA and a DBA cant get his work done.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> Previously, DBAs in our company used to have local machine administrator
> access on the SQL Server boxes. As part of tightening server security,
> those
> rights have been taken away from DBAs (in some cases they have been added
> to
> 'Power Users' group).
> In order to install SQL Server, the account need to be a local machine
> administrator (that's given otherwise installation gives error).
> But, what type of minimal rights should be given to DBAs on the server
> resources to let that person function properly?
> When I am talking about resources, I mean rights to write to specific
> directories like
> * SQL Server programs/tools
> * Common DLLs (in C:\Program files\common files...)
> * Local backup directories
> * Directory for snapshot/transactional replication transfer data.
> And right to execute programs/utilities on the server like
> * Perfmon (for system tuning/performance monitoring)
> * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> dependencies).
> Is there anything that is not needed here or, alternatively, is there
> anything that I missed?
> Another dimension of this issue is the OS login access that is needed to
> run
> the SQL Server services (needed for remote backups and replication).
> Any help will be greatly appreciated!
> --
> Regards,
> MZeeshan
>|||Thanks!
Anyone? any other ideas?
--
Regards,
MZeeshan
"ChrisR" wrote:
> At my last company, I didnt have admin rights... just SA. 90% of the time
> it was fine. The other 10% it sucked. Common things I needed someone to hold
> my hand on were:
> 1. Service restarts.
> 2. Hotfix/ service packs.
> 3. Set up stuff like Log Shipping where directory access is needed.
> 4. Wanting to just see how much disk space I had left on my backup drive.
> 5. Replication Snapshot.
> I know you already mentioned alot of these. The problem is that when the DBA
> needs these things, alot of time he needs them NOW. Not once he can have
> someone come to his desk and log in as Admin. But like I said, 90% of the
> time it was fine and I actually would prefer it. SA is usually good enough.
> Any less than SA and a DBA cant get his work done.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> > Previously, DBAs in our company used to have local machine administrator
> > access on the SQL Server boxes. As part of tightening server security,
> > those
> > rights have been taken away from DBAs (in some cases they have been added
> > to
> > 'Power Users' group).
> >
> > In order to install SQL Server, the account need to be a local machine
> > administrator (that's given otherwise installation gives error).
> >
> > But, what type of minimal rights should be given to DBAs on the server
> > resources to let that person function properly?
> >
> > When I am talking about resources, I mean rights to write to specific
> > directories like
> > * SQL Server programs/tools
> > * Common DLLs (in C:\Program files\common files...)
> > * Local backup directories
> > * Directory for snapshot/transactional replication transfer data.
> >
> > And right to execute programs/utilities on the server like
> >
> > * Perfmon (for system tuning/performance monitoring)
> > * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> > dependencies).
> >
> > Is there anything that is not needed here or, alternatively, is there
> > anything that I missed?
> >
> > Another dimension of this issue is the OS login access that is needed to
> > run
> > the SQL Server services (needed for remote backups and replication).
> >
> > Any help will be greatly appreciated!
> >
> > --
> > Regards,
> > MZeeshan
> >
>
>|||Hi MZeeshan,
If you need OS login access that is needed to run the SQL Server services
(needed for remote backups and replication). I think give DBA local
administrator privilege is necessary.
BTW, you are recommanded using the tools below to ensure the security of
your product server.
Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B
352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Minimum level of rights for a SQL Server DBA.

Previously, DBAs in our company used to have local machine administrator
access on the SQL Server boxes. As part of tightening server security, those
rights have been taken away from DBAs (in some cases they have been added to
'Power Users' group).
In order to install SQL Server, the account need to be a local machine
administrator (that's given otherwise installation gives error).
But, what type of minimal rights should be given to DBAs on the server
resources to let that person function properly?
When I am talking about resources, I mean rights to write to specific
directories like
* SQL Server programs/tools
* Common DLLs (in C:\Program files\common files...)
* Local backup directories
* Directory for snapshot/transactional replication transfer data.
And right to execute programs/utilities on the server like
* Perfmon (for system tuning/performance monitoring)
* Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
dependencies).
Is there anything that is not needed here or, alternatively, is there
anything that I missed?
Another dimension of this issue is the OS login access that is needed to run
the SQL Server services (needed for remote backups and replication).
Any help will be greatly appreciated!
Regards,
MZeeshanAt my last company, I didnt have admin rights... just SA. 90% of the time
it was fine. The other 10% it sucked. Common things I needed someone to hold
my hand on were:
1. Service restarts.
2. Hotfix/ service packs.
3. Set up stuff like Log Shipping where directory access is needed.
4. Wanting to just see how much disk space I had left on my backup drive.
5. Replication Snapshot.
I know you already mentioned alot of these. The problem is that when the DBA
needs these things, alot of time he needs them NOW. Not once he can have
someone come to his desk and log in as Admin. But like I said, 90% of the
time it was fine and I actually would prefer it. SA is usually good enough.
Any less than SA and a DBA cant get his work done.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> Previously, DBAs in our company used to have local machine administrator
> access on the SQL Server boxes. As part of tightening server security,
> those
> rights have been taken away from DBAs (in some cases they have been added
> to
> 'Power Users' group).
> In order to install SQL Server, the account need to be a local machine
> administrator (that's given otherwise installation gives error).
> But, what type of minimal rights should be given to DBAs on the server
> resources to let that person function properly?
> When I am talking about resources, I mean rights to write to specific
> directories like
> * SQL Server programs/tools
> * Common DLLs (in C:\Program files\common files...)
> * Local backup directories
> * Directory for snapshot/transactional replication transfer data.
> And right to execute programs/utilities on the server like
> * Perfmon (for system tuning/performance monitoring)
> * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> dependencies).
> Is there anything that is not needed here or, alternatively, is there
> anything that I missed?
> Another dimension of this issue is the OS login access that is needed to
> run
> the SQL Server services (needed for remote backups and replication).
> Any help will be greatly appreciated!
> --
> Regards,
> MZeeshan
>|||Thanks!
Anyone? any other ideas?
--
Regards,
MZeeshan
"ChrisR" wrote:

> At my last company, I didnt have admin rights... just SA. 90% of the time
> it was fine. The other 10% it sucked. Common things I needed someone to ho
ld
> my hand on were:
> 1. Service restarts.
> 2. Hotfix/ service packs.
> 3. Set up stuff like Log Shipping where directory access is needed.
> 4. Wanting to just see how much disk space I had left on my backup drive.
> 5. Replication Snapshot.
> I know you already mentioned alot of these. The problem is that when the D
BA
> needs these things, alot of time he needs them NOW. Not once he can have
> someone come to his desk and log in as Admin. But like I said, 90% of the
> time it was fine and I actually would prefer it. SA is usually good enough
.
> Any less than SA and a DBA cant get his work done.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
>
>|||Hi MZeeshan,
If you need OS login access that is needed to run the SQL Server services
(needed for remote backups and replication). I think give DBA local
administrator privilege is necessary.
BTW, you are recommanded using the tools below to ensure the security of
your product server.
Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/...g=en&familyid=B
352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.