Search code examples
sqlsql-serverdatabasedatabase-administrationdiskspace

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?

Solution

  • 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