locked
xp_fixeddrives and impersonation RRS feed

  • Question

  • I need to execute xp_fixeddrives and get the drive space information. Firstly, I just gave execute permission to that extended procedure and created a credential > mapped it to my sql login (which will run the extended proc) > created proxy through sql service account but it didn't work. Secondly, I created the following procedure:

    USE [master]
    GO
    /****** Object:  StoredProcedure [dbo].[Spacedetails]    Script Date: 12/20/2008 19:20:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[Spacedetails]
    WITH EXECUTE AS 'Corp\sqllogsvc'
    AS
    BEGIN
    SET NOCOUNT ON
    IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
    DROP TABLE ##_DriveSpace
    IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
    DROP TABLE ##_DriveInfo

    DECLARE @Result INT
    , @objFSO INT
    , @Drv INT
    , @cDrive VARCHAR(13)
    , @Size VARCHAR(50)
    , @Free VARCHAR(50)
    , @Label varchar(10)

    CREATE TABLE ##_DriveSpace
    (
    DriveLetter CHAR(1) not null
    , FreeSpace VARCHAR(10) not null

    )

    CREATE TABLE ##_DriveInfo
    (
    DriveLetter CHAR(1)
    , TotalSpace bigint
    , FreeSpace bigint
    , Label varchar(10)
    )

    INSERT INTO ##_DriveSpace
    EXEC master.dbo.xp_fixeddrives


    -- Iterate through drive letters.
    DECLARE curDriveLetters CURSOR
    FOR SELECT DriveLetter FROM ##_DriveSpace

    DECLARE @DriveLetter char(1)
    OPEN curDriveLetters

    FETCH NEXT FROM curDriveLetters INTO @DriveLetter
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

    SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

    EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

    IF @Result = 0

    EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

    IF @Result = 0

    EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

    IF @Result = 0

    EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

    IF @Result = 0

    EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

    IF @Result <> 0

    EXEC sp_OADestroy @Drv
    EXEC sp_OADestroy @objFSO

    SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

    SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

    INSERT INTO ##_DriveInfo
    VALUES (@DriveLetter, @Size, @Free, @Label)

    END
    FETCH NEXT FROM curDriveLetters INTO @DriveLetter
    END

    CLOSE curDriveLetters
    DEALLOCATE curDriveLetters

    PRINT 'Drive information for server ' + @@SERVERNAME + '.'
    PRINT ''

    -- Produce report.
    SELECT DriveLetter
    , Label
    , FreeSpace AS [FreeSpace MB]
    , (TotalSpace - FreeSpace) AS [UsedSpace MB]
    , TotalSpace AS [TotalSpace MB]
    , ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

    FROM ##_DriveInfo
    ORDER BY [DriveLetter] ASC
    DROP TABLE ##_DriveSpace
    DROP TABLE ##_DriveInfo

    END

    Actually i would prefer the 1st method of just allowing the sql login to run xp_fixeddrives instead of creating a sp and wrapping it with exec clause. My question is what needs to be done for non-sysadmins to run xp_fixeddrives ? I would like to give minimal permission to the sql login which will run this extended procedure.

    - Deepak


    Deepak | Mark the answers if it helps to solve your problem |
    Saturday, December 20, 2008 2:37 PM

Answers

  • Deepak,

    If you are going to make the COM/OLE Automation calls in your code anyway, you don't need to call xp_fixeddrives.  Fixed Disks on the server have a DriveType of 2.  If you iterate over the collection in the OLE calls, you can find all of the drives and get their sizes without calling GetDrive().  There is an example of how to do this on the following link:

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DiskSpaceMon


    -- Jonathan Kehayias (MCITP) | Please mark answers that solve your problem | http://www.sqlclr.net
    Monday, December 22, 2008 1:58 PM