防止 SSMS 看到伺服器的文件系統

我有幾個使用者在我的管理下共享一個 MS SQL Server 2017。他們不應該看到（甚至不知道）該伺服器上的其他使用者及其數據。每個使用者都有自己的數據庫。他們可以對他們的數據庫做任何他們想做的事情。

我正在使用 SQL Server 的Partial Containment功能來鎖定使用者。登錄是在數據庫中創建的。這很好用，因為他們看不到其他使用者帳戶或數據庫。數據庫登錄名被添加到我使用以下命令創建的數據庫角色中：

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

我新創建了一個數據庫登錄帳戶，僅將其添加到所述角色。使用者沒有其他權限（我知道）。

剩下的唯一問題是 SSMS 仍然能夠瀏覽伺服器的文件系統。如果我右鍵點擊數據庫並選擇Tasks -> Restore -> Database，然後選擇Device: -> [...]並添加文件。這允許 SSMS 瀏覽伺服器的文件系統，我想拒絕。使用者實際上無法恢復數據庫，但他可以瀏覽文件系統。

這裡的這個問題表明 SSMS 正在使用儲存過程xp_fixeddrives，xp_dirtree並且xp_fileexist. 但是，這些儲存過程在以具有所述組權限的使用者身份執行時返回空結果。我讀過這是使用者不是系統管理員角色成員時的行為。這已經讓我有些困惑，因為我明確拒絕執行 dbrole，但使用者仍然可以執行儲存過程。但是，當通過 SSMS 瀏覽文件系統時，它仍然不是空的。

SSMS 從哪裡獲取文件系統資訊，如何防止這種情況發生？

編輯：我也剛剛注意到 SSMS 能夠檢索伺服器上存在的所有數據庫備份的所有數據庫列表。同樣，我不知道它如何獲取這些資訊以及如何防止它。

跟踪查詢

跟踪執行的查詢時，會發現以下查詢一一列出驅動器上的文件夾。

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

使用的主要功能是sys.dm_os_enumerate_filesystem，對於打開的每個文件夾，它會更深一層，例如第二層：

select @Path = N'D:\Data\'

---

對於正常登錄

對於正常登錄，就像拒絕此 TVF 上的選擇權限一樣簡單，以使使用者無法列出文件夾。

DENY SELECT ON master.sys.dm_os_enumerate_filesystem TO [Domain\LoginName]

嘗試選擇備份時，使用者應該看到以下消息：

然後，使用者將只能看到驅動器號。

---

對於包含的使用者

對於被包含的使用者，直接拒絕TVF上的選擇是不行的

包含的使用者可以成功執行下一個查詢範例

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

而且….這不起作用：

use [PartialDb]
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO [PartialUser];
GO

消息 4629，級別 16，狀態 10，行 34 僅噹噹前數據庫為主數據庫時，才能授予對伺服器範圍目錄視圖或系統儲存過程或擴展儲存過程的權限。

以下語句有效，但它們不限制使用者，即使它不是dbrole角色的一部分

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];

有什麼作用？ 理論上

由於包含的使用者使用來賓帳戶/公共角色來連接並從 dmv 中選擇，（公共角色預設可以訪問某些對象）我們可以嘗試限制公共角色。

由於多種原因，這並不理想。例如，拒絕 > 授予，因此只有sysadmin角色中的成員才能從此 TVF 中進行選擇。

另一個需要注意的重要事項是，更改來賓使用者/公共角色可能會對實例或某些功能產生未知的副作用。

USE MASTER
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO public;
GO

更改公共/訪客權限不是一個理想的方案。

例如，禁用來賓使用者可能會破壞 msdb 數據庫。

在包含的使用者的上下文中重新執行選擇：

消息 229，級別 14，狀態 5，第 7 行對象“dm_os_enumerate_filesystem”、數據庫“mssqlsystemresource”、模式“sys”的 SELECT 權限被拒絕。

可能有也可能沒有辦法繞過這個遠離理想的方法，我還沒有找到。

公共角色的權限範例：

這些被授予是有原因的，因為在拒絕/撤銷這些對象時某些功能可能會中斷。謹慎行事。

有關來賓使用者/公共角色的更多資訊在這裡

引用自：https://dba.stackexchange.com/questions/246773