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?
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.
15 People are following this question.
