question

liton avatar image
liton asked

Is it normal to restrict DMV access on production server?

I’m trying to look at currently running queries by using the sys.dm_exec_requests DMV but I don’t have the permission. I also tried to look at the query stats by running sys.dm_exec_query_stats and I don’t have permission to run that DMV as well. At my previous job (small company), database developers had permission to run those DMV. Do most companies usually restrict developers to run DMVs? What are the benefits of denying DMV access to developers? 
dmvpermission
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
The default standard for most DBAs would be to restrict access to this, yes. Now we're getting into opinion. I think that view_database_state and view_server_state should be granted to development teams, especially if those teams are tasked with writing and tuning T-SQL. There is no other way to safely allow the development team to gather the needed data except through access to the DMVs. Now, there are a couple of DMVs I would either try to exclude or at least educate the developers that they must not, under any circumstance, run queries against them (first one that comes to mind is the one for retrieving index physical stats, that can seriously hurt the system). Other than that though, the DMVs are pretty benign. Developers should be given access to them.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.