Prevent SSMS from seeing the server's file system
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