2

I have a SQL account with no permissions other than having the public role. I have run the following command to deny permissions to the system views:

  use master
    DENY VIEW ANY DEFINITION TO public;

However when I query view like sys.databases I still get metadata returned. My understanding was this should not be the case?

Tom
  • 1,569
  • 6
  • 29
  • 43

1 Answers1

4

This is because VIEW [ANY] DEFINITION doesn't have anything to do with retrieving results that are exposed to public by default; this has to do with viewing the definition of objects, for example this should no longer be allowed:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.databases'));

Try:

DENY SELECT ON sys.databases TO public;

Or, more broadly:

DENY SELECT ON SCHEMA::sys TO public;

Note that this may cause other issues, for example I am not sure what will happen if the user tries to connect to SQL Server using Management Studio - Object Explorer probably will not load completely; IntelliSense and other features may stop working as well, since they rely on metadata access.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624