Non administrators running perfmon have no remote SQL counters

Not sure if i am posting this in the right area as although my symptoms
affect SQL they are probably not specifically limited to this. This could
probably be posted in Windows 2000 security area as well.
Server spec - HP Proliant server running Windows 2000 SP4, latest hotfixes
and fully patched, SQL Server 2000 and fully patched.
Database administration team wish to run system monitor / perfmon against
the server to baseline and monitor performance but segregation of duties
ensure that these can't be made local server administrators (I don't make the
rules I only try to work within them)
As an admin if I run perfmon locally on the server itself I can see all the
SQL performance objects. If I select counters from the computer remotely all
SQL counters are displayed - on this basis I know it is not a problem with
the performance counters themselves.
I have followed KB300702 to grant the non admin users access to perfmon
remotely and this works a treat except when the user fires up perfom and
lists the performance objects all of the SQL entries are missing. Even
though I have applied the access to the SQL service performance counters as
specified in the KB this still doesn't work.
I need to get this working without granting these users full administrator
rights or any of the other built in Windows groups OR without running the SQL
services with a different user ID /password and providing this to the
database team.
Any help that anyone can provide I will accept with extreme gratitudeHi
Windows 2000 is not listed as one of the platforms in this KB'
http://support.microsoft.com/default.aspx?scid=kb;en-us;152513
in particular permissions on
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services
John
"Brian" wrote:
> Not sure if i am posting this in the right area as although my symptoms
> affect SQL they are probably not specifically limited to this. This could
> probably be posted in Windows 2000 security area as well.
> Server spec - HP Proliant server running Windows 2000 SP4, latest hotfixes
> and fully patched, SQL Server 2000 and fully patched.
> Database administration team wish to run system monitor / perfmon against
> the server to baseline and monitor performance but segregation of duties
> ensure that these can't be made local server administrators (I don't make the
> rules I only try to work within them)
> As an admin if I run perfmon locally on the server itself I can see all the
> SQL performance objects. If I select counters from the computer remotely all
> SQL counters are displayed - on this basis I know it is not a problem with
> the performance counters themselves.
> I have followed KB300702 to grant the non admin users access to perfmon
> remotely and this works a treat except when the user fires up perfom and
> lists the performance objects all of the SQL entries are missing. Even
> though I have applied the access to the SQL service performance counters as
> specified in the KB this still doesn't work.
> I need to get this working without granting these users full administrator
> rights or any of the other built in Windows groups OR without running the SQL
> services with a different user ID /password and providing this to the
> database team.
> Any help that anyone can provide I will accept with extreme gratitude|||John
Thanks for the response - you are correct in that the KB isn't listed as a
solution for Windows 2000 but a degree of commonality does exist - e.g. the
read permission required to the perflib area of the registry and the need for
full control on the performance key for each service.
The interesting bits (as I read it) seem to be that the remote counters are
running as a thread on the winlogon process and the idea that an issue with
an extensible DLL may be the problem - I will play around with this on my
test box and let you know how I get on.
Thanks again for your help
Brian
"John Bell" wrote:
> Hi
> Windows 2000 is not listed as one of the platforms in this KB'
> http://support.microsoft.com/default.aspx?scid=kb;en-us;152513
> in particular permissions on
> HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services
> John
> "Brian" wrote:
> > Not sure if i am posting this in the right area as although my symptoms
> > affect SQL they are probably not specifically limited to this. This could
> > probably be posted in Windows 2000 security area as well.
> >
> > Server spec - HP Proliant server running Windows 2000 SP4, latest hotfixes
> > and fully patched, SQL Server 2000 and fully patched.
> >
> > Database administration team wish to run system monitor / perfmon against
> > the server to baseline and monitor performance but segregation of duties
> > ensure that these can't be made local server administrators (I don't make the
> > rules I only try to work within them)
> >
> > As an admin if I run perfmon locally on the server itself I can see all the
> > SQL performance objects. If I select counters from the computer remotely all
> > SQL counters are displayed - on this basis I know it is not a problem with
> > the performance counters themselves.
> >
> > I have followed KB300702 to grant the non admin users access to perfmon
> > remotely and this works a treat except when the user fires up perfom and
> > lists the performance objects all of the SQL entries are missing. Even
> > though I have applied the access to the SQL service performance counters as
> > specified in the KB this still doesn't work.
> >
> > I need to get this working without granting these users full administrator
> > rights or any of the other built in Windows groups OR without running the SQL
> > services with a different user ID /password and providing this to the
> > database team.
> >
> > Any help that anyone can provide I will accept with extreme gratitude|||Hi
There is also http://support.microsoft.com/?id=812915 which may not be your
problem but implies it could be worth apply the hotfix/service pack if you
have not already done so.
John
"Brian" wrote:
> John
> Thanks for the response - you are correct in that the KB isn't listed as a
> solution for Windows 2000 but a degree of commonality does exist - e.g. the
> read permission required to the perflib area of the registry and the need for
> full control on the performance key for each service.
> The interesting bits (as I read it) seem to be that the remote counters are
> running as a thread on the winlogon process and the idea that an issue with
> an extensible DLL may be the problem - I will play around with this on my
> test box and let you know how I get on.
> Thanks again for your help
> Brian
>
> "John Bell" wrote:
> > Hi
> >
> > Windows 2000 is not listed as one of the platforms in this KB'
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;152513
> > in particular permissions on
> > HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services
> >
> > John
> >
> > "Brian" wrote:
> >
> > > Not sure if i am posting this in the right area as although my symptoms
> > > affect SQL they are probably not specifically limited to this. This could
> > > probably be posted in Windows 2000 security area as well.
> > >
> > > Server spec - HP Proliant server running Windows 2000 SP4, latest hotfixes
> > > and fully patched, SQL Server 2000 and fully patched.
> > >
> > > Database administration team wish to run system monitor / perfmon against
> > > the server to baseline and monitor performance but segregation of duties
> > > ensure that these can't be made local server administrators (I don't make the
> > > rules I only try to work within them)
> > >
> > > As an admin if I run perfmon locally on the server itself I can see all the
> > > SQL performance objects. If I select counters from the computer remotely all
> > > SQL counters are displayed - on this basis I know it is not a problem with
> > > the performance counters themselves.
> > >
> > > I have followed KB300702 to grant the non admin users access to perfmon
> > > remotely and this works a treat except when the user fires up perfom and
> > > lists the performance objects all of the SQL entries are missing. Even
> > > though I have applied the access to the SQL service performance counters as
> > > specified in the KB this still doesn't work.
> > >
> > > I need to get this working without granting these users full administrator
> > > rights or any of the other built in Windows groups OR without running the SQL
> > > services with a different user ID /password and providing this to the
> > > database team.
> > >
> > > Any help that anyone can provide I will accept with extreme gratitude

nolock

Hi,
i wonder how to set the nolock option.
if i use subviews do i have to go througg all of them to find all
tables and add nolock or does it affect the subqueries automatically?
is there a database switch i can set which
affects all select statements if that wont work to save time
select vp_id, Sum(summe_drawing) as summe_drawing, sum(summe_opp) as
summe_opp, sum(summe_quota) as summe_quota, product_id
from mpc_draw_opp_quota_v (nolock) '
greets mikeWhen specifying join hints use the WITH keyword, e.g.
select <column list>
from <table> with(<hint> [, ...])
Specify hints for all tables in the from clause, but be really, really
careful when using hints - they might have a negative impact on performance
and/or yield unexpected results.
ML
http://milambda.blogspot.com/|||set transaction isolation level read uncommitted
select * from yourview
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<peppi911@.hotmail.com> wrote in message
news:1137681397.337443.5080@.z14g2000cwz.googlegroups.com...
> Hi,
> i wonder how to set the nolock option.
> if i use subviews do i have to go througg all of them to find all
> tables and add nolock or does it affect the subqueries automatically?
> is there a database switch i can set which
> affects all select statements if that wont work to save time
> select vp_id, Sum(summe_drawing) as summe_drawing, sum(summe_opp) as
> summe_opp, sum(summe_quota) as summe_quota, product_id
> from mpc_draw_opp_quota_v (nolock) '
> greets mike
>|||NOLOCK will make your queries return incorrect results at lightning speed.
You shouldn't use it unless there is an overwhelming need to do so, or if
the impact of changes is minimal: for example, if you're computing an
average based on thousands of rows, it doesn't really matter if a few rows
are modified during the calculation. Under no circumstances should you use
NOLOCK to compute changes that are about to be applied to the database,
unless you use some other mechanism to serialize access to the source
tables. For example, if you're calculating payroll, and a change to an
employee's salary is rolled back after it's read out, then the amounts on
the paycheck will be incorrect. If, on the other hand, you use an
application lock to block updates to the employee table during the payroll
calculation, then you can use NOLOCK.
The READ UNCOMMITTED isolation level and the NOLOCK hint are often misused
by neophytes in an attempt to improve performance or to deal with deadlocks.
SQL Server 2005 has a new isolation level READ COMMITTED SNAPSHOT which can
be used to improve the performance of database queries and to reduce
blocking and deadlocks because it does not apply locks. It should NOT be
used to calculate changes to the database, however, because it's possible
for the information read out to become stale by the time the changes are
committed.
<peppi911@.hotmail.com> wrote in message
news:1137681397.337443.5080@.z14g2000cwz.googlegroups.com...
> Hi,
> i wonder how to set the nolock option.
> if i use subviews do i have to go througg all of them to find all
> tables and add nolock or does it affect the subqueries automatically?
> is there a database switch i can set which
> affects all select statements if that wont work to save time
> select vp_id, Sum(summe_drawing) as summe_drawing, sum(summe_opp) as
> summe_opp, sum(summe_quota) as summe_quota, product_id
> from mpc_draw_opp_quota_v (nolock) '
> greets mike
>