Monitoring SQL 2K5 in low privilege environments

If you're following good security practise and planning to limit SCOM access to monitored SQL server instances watch out for a gotcha in the SQL management pack documentation. The documentation states (page 14) that the agent action account used for monitoring SQL objects only requires DBO privilege, however after trying this and having difficulty getting some monitoring data I found that the agent action account actually needs sysadmin role privilege over the entire instance. If you just grant 'DBO' as per the guide you will find some monitoring doesn't work, e.g. SQL agent jobs, DB size etc.

After some testing and talking to MS they confirmed this behavior, but I'm not sure if they're going to update the guide. Bit surprised not to have seen more cases where people have run into this but a lot of sites will just leave Builtin/administrators in the Sysadmin role so perhaps not that many people have run into this yet.
 

Steve

Published 05 October 2008 23:58 by Steve