1

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?

3

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.

  • Makes sense thanks, we were wondering to what degree to lock down Public. Not doing this means we have an issue where people can query certain metadata and execute system procs. I would be interested to know what other people do in this regard. – Tom Oct 2 '15 at 13:58
  • @Tom Querying metadata is kind of essential, it is easy to take away specific system procedure access that you have concerns about - are there specific ones you're worried about? – Aaron Bertrand Oct 2 '15 at 18:44
  • we were wondering if the rights to query meta data was a security hole in itself as its quite easy to have a login knocking around with public rights only. As for which system stored procs am I worried about I honestly couldn't say that I have a list, at this moment in time everything hah! – Tom Oct 4 '15 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.