sql server新用户授权问题

Question

sql server中创建了新的账号，并授权了其中一个数据库的权限，但在登录新账号后，也显示出了其他未授权数据库的名称，虽然无法访问，但有没有方法让他只显示有权限的数据库

Answer 1

在 SQL Server 中，当你创建一个新账号并授予其访问特定数据库的权限时，默认情况下，该账号仍然可以看到所有数据库的名称。这是因为 sys.databases 视图对所有用户都是可见的，即使他们没有访问这些数据库的权限。

要实现只显示有权限的数据库，可以通过以下几种方法：

方法 1：使用视图过滤数据库

你可以创建一个视图，过滤掉用户没有权限的数据库，并让用户只能访问这个视图。

步骤：

1. 创建视图：

   在 master 数据库中创建一个视图，过滤掉用户没有权限的数据库。

   USE master;
   GO
   
   CREATE VIEW vw_AccessibleDatabases
   AS
   SELECT name
   FROM sys.databases
   WHERE HAS_DBACCESS(name) = 1;
   GO
   

2. 授予用户访问视图的权限：

   GRANT SELECT ON vw_AccessibleDatabases TO [YourNewUsername];
   GO
   

3. 用户查询数据库：

   用户只能通过这个视图查询数据库名称。

   USE master;
   GO
   
   SELECT name FROM vw_AccessibleDatabases;
   GO
   

方法 2：使用存储过程过滤数据库

你可以创建一个存储过程，过滤掉用户没有权限的数据库，并让用户只能通过这个存储过程查询数据库。

步骤：

1. 创建存储过程：

   在 master 数据库中创建一个存储过程，过滤掉用户没有权限的数据库。

   USE master;
   GO
   
   CREATE PROCEDURE sp_AccessibleDatabases
   AS
   BEGIN
       SELECT name
       FROM sys.databases
       WHERE HAS_DBACCESS(name) = 1;
   END
   GO
   

2. 授予用户执行存储过程的权限：

   GRANT EXECUTE ON sp_AccessibleDatabases TO [YourNewUsername];
   GO
   

3. 用户查询数据库：

   用户只能通过这个存储过程查询数据库名称。

   USE master;
   GO
   
   EXEC sp_AccessibleDatabases;
   GO
   

方法 3：使用安全视图

你可以创建一个安全视图，并使用 WITH SCHEMABINDING 和 SCHEMABINDING 选项来确保视图的安全性。

步骤：

1. 创建安全视图：

   在 master 数据库中创建一个安全视图，过滤掉用户没有权限的数据库。

   USE master;
   GO
   
   CREATE VIEW vw_SecureAccessibleDatabases
   WITH SCHEMABINDING
   AS
   SELECT name
   FROM sys.databases
   WHERE HAS_DBACCESS(name) = 1;
   GO
   

2. 授予用户访问视图的权限：

   GRANT SELECT ON vw_SecureAccessibleDatabases TO [YourNewUsername];
   GO
   

3. 用户查询数据库：

   用户只能通过这个安全视图查询数据库名称。

   USE master;
   GO
   
   SELECT name FROM vw_SecureAccessibleDatabases;
   GO
   

总结

通过以上方法，你可以限制用户只能看到他们有权限访问的数据库。视图和存储过程是实现这一目标的有效手段。你可以根据具体需求选择适合的方法。