Help: SQL Server

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 below stored procedure which would provide the details about disk space. This has taken from internet and I am not author of this. I have done little modification to original code to get same output as xp_fixeddrives.

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

• To test this we need to create a new SQL login, user and grant execute permission on the stored proc created.

-- 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

• To test the functionality you need to login with AppAdmin account and run procedure as below.
• — Execute procedure
  exec Usp_fixeddrives

     /*————————
     execute Usp_fixeddrives
      ————————*/
      drive MB free
      —– ——————–
      C     23523
      D     196197

• Here is the output from sysadmin account and they are matching!!!

      /*————————
      execute xp_fixeddrives
      ————————*/
      drive MB free
      —– ———–
      C     23523
      D     196197

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani