Tracing the queries
When tracing the queries executed, below query is found that lists the folders on the drives one by one.
declare @Path nvarchar(255)
declare @Name nvarchar(255)
select @Path = N'D:\'
select @Name = null;
create table #filetmpfin (Name nvarchar(255) NOT NULL, IsFile bit NULL, FullName nvarchar(300) not NULL)
declare @FullName nvarchar(300)
if exists (select 1 from sys.all_objects where name = 'dm_os_enumerate_filesystem' and type = 'IF' and is_ms_shipped = 1)
begin
if (@Name is null)
begin
insert #filetmpfin select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0
end
if (NOT @Name is null)
begin
if(@Path is null)
select @FullName = @Name
else
select @FullName = @Path + convert(nvarchar(1), serverproperty('PathSeparator')) + @Name
create table #filetmp3 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL )
insert #filetmp3 select file_exists, file_is_a_directory, parent_directory_exists from sys.dm_os_file_exists(@FullName)
insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp3 where Exist = 1 or IsDir = 1
drop table #filetmp3
end
end
else
begin
if(@Name is null)
begin
if (right(@Path, 1) = '\')
select @Path= substring(@Path, 1, len(@Path) - charindex('\', reverse(@Path)))
create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL )
insert #filetmp EXECUTE master.dbo.xp_dirtree @Path, 1, 1
insert #filetmpfin select Name, IsFile, @Path + '\' + Name from #filetmp f
drop table #filetmp
end
if(NOT @Name is null)
begin
if(@Path is null)
select @FullName = @Name
else
select @FullName = @Path + '\' + @Name
if (right(@FullName, 1) = '\')
select @Path= substring(@Path, 1, len(@FullName) - charindex('\', reverse(@FullName)))
create table #filetmp2 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL )
insert #filetmp2 EXECUTE master.dbo.xp_fileexist @FullName
insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp2 where Exist = 1 or IsDir = 1
drop table #filetmp2
end
end
SELECT
Name AS [Name],
IsFile AS [IsFile],
FullName AS [FullName]
FROM
#filetmpfin
ORDER BY
[IsFile] ASC,[Name] ASC
drop table #filetmpfin
The main function used is sys.dm_os_enumerate_filesystem, for each folder that is opened, it goes a level deeper, an example of a second level:
select @Path = N'D:\Data\'
For regular logins
For regular logins it is as easy as denying the select permissions on this TVF to make the user not able to list the folders.
DENY SELECT ON master.sys.dm_os_enumerate_filesystem TO [Domain\LoginName]
When trying to select a backup, the user should see this message:
The user will then be able to only see the drive letters.
For contained users
For the contained user, denying the select on the TVF directly does not work
The contained user can succesfully run the next query example
declare @Path nvarchar(255)
declare @Name nvarchar(255)
select @Path = N'D:\'
select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0
And.... this does not work:
use [PartialDb]
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO [PartialUser];
GO
Msg 4629, Level 16, State 10, Line 34 Permissions on server scoped
catalog views or system stored procedures or extended stored
procedures can be granted only when the current database is master.
Below statements work but they do not restrict the user, even if it is not part of the dbrole role
DENY VIEW DATABASE STATE TO [PartialUser];
DENY VIEW DEFINITION ON SCHEMA :: information_schema TO [PartialUser];
DENY VIEW DEFINITION ON SCHEMA :: sys TO [PartialUser];
DENY SELECT ON SCHEMA :: information_schema TO [PartialUser];
DENY SELECT ON SCHEMA :: sys TO [PartialUser];
What does work? In theory
Since the contained user uses the guest account / public role to connect and select from dmv's, (the public role has access to certain objects by default) we could try restricting the public role.
This is not ideal due to a number of reasons. For example, deny > grant and as a result only members in the sysadmin role will be able to select from this TVF.
Another important thing to note is that changing the guest user / public role can have unknown side-effects on the instance or certain functionalities.
USE MASTER
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO public;
GO
Changing public / guest permissions is not an ideal scenario.
For example, disabling the guest user can break the msdb database.
Rerunning the select in the context of the contained user:
Msg 229, Level 14, State 5, Line 7 The SELECT permission was denied on
the object 'dm_os_enumerate_filesystem', database
'mssqlsystemresource', schema 'sys'.
There may or may not be a way around this far from ideal approach, I have not found it.
An example of the public role's permissions:
These are granted for a reason, as such certain functionalities might break when denying / revoking these objects. Proceed with caution.
Some more information on the guest user / public role here
