Help : Getting drive free space details without sysadmin permission
Posted by blakhani on July 15, 2011
Recently I worked with a customer where they wanted to run xp_fixeddrives without sysadmin permissions. This procedure was running from an application which was using non-sysadmin account. The major challenge in helping on such cases is that xp_fixeddrives is “undocumented” and hence “unsupported”
I love my customers and hate to say “No” and send them back empty handed. So, I put a little efforts to help them and found way to do it using proxy account. Here are the steps.
- Enable xp_cmdshell if its not enabled already.
sp_configure 'show advanced options', 1 go reconfigure with override go sp_configure 'xp_cmdshell', 1 go reconfigure with override
CREATE PROC Usp_fixeddrives WITH EXECUTE AS OWNER AS BEGIN set nocount on DECLARE @Drive TINYINT, @SQL VARCHAR(100) SET @Drive = 97 DECLARE @Drives TABLE ( drive CHAR(1), info VARCHAR(80)) WHILE @Drive <= 122 BEGIN SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR( @Drive) + ':''' INSERT @Drives (info) EXEC (@SQL) UPDATE @Drives SET drive = CHAR(@Drive) WHERE drive IS NULL SET @Drive = @Drive + 1 END -- Show the expected output SELECT upper(drive) AS drive, cast (SUM(CASE WHEN info LIKE 'Total # of free bytes : %' THEN CAST( REPLACE( Substring(info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024.0 as bigint) AS 'MB free' FROM (SELECT drive, info FROM @Drives WHERE info LIKE 'Total # of %') AS d GROUP BY drive ORDER BY drive END
- Since we are not going to give permission to SQL login on xp_cmdshell so we need to use proxy account. Windows account would be a Admin account.
EXEC sp_xp_cmdshell_proxy_account 'Domain\User', 'Password_For_User'; GO
-- Create login USE [master] GO CREATE LOGIN [AppAdmin] WITH PASSWORD=N'P@ssw0rd1', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO -- Create User USE [master] GO CREATE USER [AppAdmin] FOR LOGIN [AppAdmin] GO -- Grant execute on the stored procedure. Grant exec on Usp_fixeddrives to [AppAdmin] GO
drive MB free
- Here is the output from sysadmin account and they are matching!!!
drive MB free
Hope this would help someone in the world!