禁止使用者查詢內部SQL Server資訊

我試圖禁止數據庫使用者獲取 SQL 伺服器內部資訊。

創建數據庫使用者的腳本：

CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: sys TO [testuser] 
DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: INFORMATION_SCHEMA TO [testuser] 
GRANT SELECT on SCHEMA::dbo to [testuser]
DENY INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: dbo TO [testuser] 

問題是使用者仍然能夠執行如下查詢：

EXECUTE AS USER   = 'testuser';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token

你有什麼想法？

恐怕沒有辦法拒絕任何人查看此類資訊的權限。但是，模擬數據庫使用者不足以查看對伺服器範圍的 DMV 或功能的影響，例如sys.login_token：您需要模擬登錄。

CREATE LOGIN ZeroCool WITH PASSWORD = 'ICantSeeCoolInfo', CHECK_POLICY = OFF;
GO

EXECUTE AS LOGIN = 'ZeroCool';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token
GO

REVERT;

結果：

------------------------------------------------------------------------ -----------------------------------------------
master                                                                   Microsoft SQL Server 2012 - 11.0.5613.0 (X64) 
   May  4 2015 19:05:02 
   Copyright (c) Microsoft Corporation
   Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


(1 row(s) affected)


--------------
Shared memory

(1 row(s) affected)

principal_id sid                                  name         type          usage          
------------ ------------------------------------ ------------ ------------- ---------------
301          0x34FA109FE0F7694E866DF0F9E82BB99B   ZeroCool     SQL LOGIN     GRANT OR DENY
2            0x02                                 public       SERVER ROLE   GRANT OR DENY

(2 row(s) affected)

我看不出阻止使用者看到這些資訊將如何幫助您加強安全性：只需實施安全最佳實踐，您就可以了。

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