Add the right permissions to a user to use sys.dm_os_volume_stats table function

I'm trying to get the total disk size from the current database that I'm calling in SQL Server, but the user under which the call is being done doesn't have the right permissions to execute this function.

I'm using this script

SELECT distinct(volume_mount_point), total_bytes/1048576/1024 as Size_in_GB, available_bytes/1048576/1024 as Free_in_GB, (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) group by volume_mount_point, total_bytes/1048576, available_bytes/1048576 order by 1 

I also tried using xp_cmdshell but some of the servers that this application will be running at, for some IT concerns won't let us use it.

I have partial results by using EXEC master.dbo.xp_fixeddrives but it only gives me the Free Space Available, I still need the used space.

Is there any way to script out the right permissions to allow my user to use sys.dm_os_volume_stats?

2 Answers

From the documentation for that DMO, the login needs the VIEW SERVER STATE permission. NB - that permission gives a lot of other things besides the ability to read from that DMO, so evaluate that and assess whether that's appropriate in your environment.

I resolved the issue by adding "WITH EXECUTE AS OWNER" to resolve my issue

CREATE PROCEDURE [dbo].[GetPerformanceStatus] WITH EXECUTE AS OWNER AS BEGIN ... END

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobHJvaGWAeoCOmpudZaSdsm6%2ByKCfrWWgmr%2ButdKsoKimo2LBsHnAZqysnaJiwbB51KycZqupqHqluYyoqmaun6HCrrGMrKuarKNiwaKuy55kn62emMGqu80%3D