Get SQL Server free disk information without admin privileges

Following script will get the free disk space in database server. To run this script following permissions are required. "NO sysadmin" privileges are required.

Permissions Required

GRANT execute on sp_OAGetErrorInfo to UuerUser
GRANT execute on sp_OAGetProperty to UuerUser
GRANT execute on sp_OACreate to UuerUser
GRANT execute on sp_OADestroy to UuerUser

Script

declare @RunLocal bit = 1;

DECLARE @DiskSpaceStats TABLE
(
DriveLetter char(1),
TotalSize decimal(20,0),
AvailableBytes decimal(20,0),
DriveType char(10),
PercentFree decimal(18,3),
Report_Date datetime default getdate()
)

DECLARE @DiskSpace TABLE
(
RowID int Identity Primary Key,
DriveLetter char(1),
TotalSize decimal(20,0),
AvailableBytes decimal(20,0),
DriveType char(10),
PercentFree decimal(18,3)
)

DECLARE @counter int
DECLARE @FSOobject int
DECLARE @RecordCount int
DECLARE @count int
DECLARE @string  char(35)
DECLARE @DriveLetter char(1)
DECLARE @TotalSize char(50)
DECLARE @AvailableSpace char(50)
DECLARE @DriveType char(10)
DECLARE @error int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @ServerName sysname

SET @ServerName = @@ServerName
SET @count=0
SET @counter=67
SET @error=0

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSOobject OUT

IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END

WHILE @counter < 91 OR @error <3 p="p"> BEGIN
SET @String='Drives.item("'+char(@counter)+'").DriveLetter'
EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveLetter OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
SELECT @counter=@counter+1
SELECT @error=@error +1
GOTO ErrorPoint
END

SET @String='Drives.item("'+char(@counter)+'").TotalSize'
EXEC @HR = sp_OAGetProperty @FSOobject, @string, @TotalSize OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
SELECT @counter=@counter+100
SELECT @error=1
END

SET @String='Drives.item("'+char(@counter)+'").AvailableSpace'
EXEC @HR = sp_OAGetProperty @FSOobject, @string, @AvailableSpace OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END

SET @String='Drives.item("'+char(@counter)+'").DriveType'
EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveType OUT
IF @HR <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
PRINT @desc
RETURN
END

IF @Error < 3
BEGIN
INSERT INTO @DiskSpace (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
VALUES (@DriveLetter, @TotalSize, @AvailableSpace, @DriveType, (convert(decimal(20,0), @AvailableSpace)/convert(decimal(20,0), @TotalSize)))
END

SELECT @counter=@counter+1
END

ErrorPoint:

INSERT INTO @DiskSpaceStats (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
SELECT DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree
FROM @DiskSpace

EXEC @hr = sp_OADestroy @FSOobject
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSOobject
RETURN
END

SELECT * FROM @DiskSpace

Comments

Popular posts from this blog

Embedding PowerBI with ASP.NET Core 2 and Angular(2,4,5)

Handling Exit Event of Console Application in C#

CSS text-overflow ellipsis in the beginning of the the text - Left Ellipsis using CSS in a Flex box