Running DMVs--what priviliges are required?

[jsteph]

Hi all,
I would like to be able to run some of the DMV's and have read access to our production server. I'm manager now, but I have a fair amount of experience doing dba tasks.

So, from a segregation-of-duties standpoint, I understand that I should not be able to affect change on the production servers, but in our situation I very much need to be able to do some initial troubleshooting so when I call our 3rd party infrastructure group I will have diagnosed the issue or at least ruled out certain things, saving precious time. We have no one else on site for this so my skillset fits the bill for what is needed, but the role of manager may have a conflict with SOD best practices.

In the past I had sysadmin rights, but I know this will be too much for my current role and would be a red flag in an SOD/Security audit. So the question is, what server role would have rights to DMV's, view rights to user-database code and data, but be restricted from, say, editing a stored procedure or making any substantive changes on the server. From a privacy standpoint, I already have access to all user (customer) database data via the front-end.
Thanks,
--Jim

 

[gmmastros]

According to:

http://msdn.microsoft.com/en-us/library/ms188754.aspx

You need to have select permission on the DMV and you need to have VIEW SERVER STATE and/or VIEW DATABASE STATE.

Some DMV's are server specific so VIEW SERVER STATE is required for those. Some are database specific so VIEW DATABASE STATE is required for those.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jsteph]

Thanks, that should get me going,
--Jim