Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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

Advertisements

3 Responses to “Help : Getting drive free space details without sysadmin permission”

  1. I think Powershell job steps and proxies are much better for this purpose. xp_cmdshell is way too big a security risk to even consider it.

    Nice script though 🙂

  2. LONG Bora said

    I can’t EXEC sp_xp_cmdshell_proxy_account ‘Domain\User’, ‘Password_For_User’; with my domain and userpassword.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: