SQL Server 2019：如何禁止用户查询数据库结构及解决权限失效问题？

一、权限被覆盖的原因及解决方法 #

你的DENY命令未生效，大概率是以下两种角色权限覆盖导致：

1. 高权限固定角色成员身份
   如果当前用户是sysadmin、serveradmin等服务器级固定角色，或是db_owner数据库级固定角色的成员，SQL Server会直接忽略所有DENY权限——这类高权限角色的权限优先级远高于显式设置的DENY规则。你可以执行以下语句检查用户所属角色：

   -- 检查服务器角色
   SELECT r.name AS ServerRole
   FROM sys.server_principals sp
   JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
   JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
   WHERE sp.name = CURRENT_USER;
   
   -- 检查数据库角色
   SELECT r.name AS DatabaseRole
   FROM sys.database_principals dp
   JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
   JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
   WHERE dp.name = CURRENT_USER;
   

   若用户属于上述高权限角色，必须先将其移除，后续的DENY命令才会生效。

2. public角色的默认权限
   所有数据库用户默认都属于public角色，而public默认拥有对sys架构下多数系统视图的SELECT权限。仅对用户单独设置DENY可能被public角色的GRANT权限抵消，需要直接对用户设置明确的DENY：

   -- 禁止用户查询sys架构下所有对象
   DENY SELECT ON SCHEMA::sys TO [你的用户名];
   -- 禁止用户查询INFORMATION_SCHEMA架构下所有对象
   DENY SELECT ON SCHEMA::INFORMATION_SCHEMA TO [你的用户名];
   -- 补充禁止服务器级的视图定义权限（若需要）
   DENY VIEW ANY DEFINITION TO [你的登录名];
   

   注意：需区分数据库用户和服务器登录名，VIEW ANY DEFINITION是服务器级权限，需针对登录名设置。

二、自定义权限违反的错误提示 #

SQL Server无法直接修改系统权限拒绝的默认错误信息，但可以通过数据库级DML触发器实现拦截并抛出自定义错误。具体步骤如下：

1. 创建触发器，捕获针对sys和INFORMATION_SCHEMA架构的SELECT操作：

   CREATE TRIGGER BlockSystemSchemaQueries
   ON DATABASE
   FOR SELECT
   AS
   BEGIN
       SET NOCOUNT ON;
       -- 检查当前查询是否针对sys或INFORMATION_SCHEMA架构
       IF EXISTS (
           SELECT 1
           FROM sys.dm_exec_requests req
           JOIN sys.dm_exec_sql_text(req.sql_handle) st ON 1=1
           WHERE req.session_id = @@SPID
           AND (st.text LIKE '%sys.%' OR st.text LIKE '%INFORMATION_SCHEMA.%')
       )
       BEGIN
           -- 抛出自定义错误，错误号需大于50000
           RAISERROR('禁止查询系统视图及INFORMATION_SCHEMA架构数据，请联系管理员。', 16, 1);
           ROLLBACK;
       END
   END;
   

2. 若需临时关闭触发器，执行：

   DISABLE TRIGGER BlockSystemSchemaQueries ON DATABASE;
   

   注意：该触发器会拦截所有包含sys.或INFORMATION_SCHEMA.的查询，若有合法的系统视图查询需求，需调整触发器的判断逻辑（比如排除特定用户或特定对象）。

内容的提问来源于stack exchange，提问作者Amber Cahill