مشاركة عبر

  • مقالة

VIEW ANY DATABASE Permission

VIEW ANY DATABASE permission is a new, server-level permission. A login that is granted this permission can see metadata that describes all databases, regardless of whether the login owns or can actually use a particular database.

The VIEW ANY DATABASE permission regulates the exposure of metadata in the sys.databases and sys.sysdatabases views, and the sp_helpdb system stored procedure.

By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server can see all databases in the instance. To verify this behavior, run the following query:

SELECT l.name as grantee_name, p.state_desc, p.permission_name 
FROM sys.server_permissions AS p JOIN sys.server_principals AS l 
ON   p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;
GO

To grant the VIEW ANY DATABASE permission to a specific login, run the following query:

GRANT VIEW ANY DATABASE TO <login>;

The metadata that describes the master and tempdb databases is always visible to public.

Members of the sysadmin fixed server role can always see all database metadata.

Database owners can always see rows in sys.databases for databases that they own.

Granting the CREATE DATABASE and ALTER ANY DATABASE permissions to a login confers access to the database metadata.

Note

CREATE DATABASE permission is implicit to the dbcreator fixed server role.

Note

If you grant CREATE DATABASE and ALTER ANY DATABASE permissions to a login and do not deny VIEW ANY DATABASE to the login, the login can see all rows in sys.databases.

To limit visibility to database metadata, deny a login the VIEW ANY DATABASE permission. After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns. For more information, see DENY Server Permissions (Transact-SQL).