Prevent SSMS from seeing the server's file system

I have several users who share an MS SQL server 2017 under my administration. They should not see (or even be aware) of the other users and their data on that server. Each user has his own database. They can do whatever they want with their database.

I'm using SQL Server's Partial Containment feature to lock the users in place. The logins are created inside the database. This works well, as they don't see other user accounts or databases this way. The DB logins are added to a Database Role that I create with this command:

USE dbname
CREATE ROLE dbrole
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, CREATE VIEW, ALTER ANY SCHEMA TO dbrole
DENY EXECUTE TO dbrole

I freshly create a db login account and only add it to said role. The user has no other permissions (that I'm aware of).

The only problem left is that SSMS is still able to browse the server's file system. If I right click on the database and choose Tasks -> Restore -> Database, then select Device: -> [...] and Add a file. This allows SSMS to browse the server's file system, which I would like to deny. The user cannot actually restore the DB, but he can browse the file system.

This question here suggests that SSMS is using the stored procedures xp_fixeddrives, xp_dirtree and xp_fileexist. However, those stored procedures return empty results when executed as a user with the permissions of said group. I've read that this is the behaviour when a user is not a member of the sysadmin role. This already confuses me slightly since I explicitely deny EXECUTE to the dbrole, yet the user can still execute the stored procedures. But still, when browsing the file system through SSMS, it is not empty.

Where does SSMS get the file system information from and how can I prevent this?

edit: I also just noticed that SSMS is able to retrieve a list of all DB backups existing on the server for all databases. Again, I don't how it gets this information and how I can prevent it.

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_en

StackExchange Database Administrators Q#246773, answer score: 11