MS Azure SQL - How to restrict User to one [custom] schema without the ability to access [sys] and [INFORMATION_SCHEMA]

Question

I've performed the following:

• created a new [custom] schema on the [customer_db]
• created new [login] & [user] in the [master] and corresponding user in the [customer_db]
• granted 'db_datareader' and 'db_denydatawriter' to [user]
• ALTER AUTHORIZATION ON SCHEMA::custom TO [user]
• DENY SELECT ON schema::[dbo] TO [user]
• DENY SELECT ON schema::[sys] TO [user]
• DENY SELECT ON schema::[INFORMATION_SCHEMA] TO [user]

...but when I am trying this new, restricted login it still allows me to see all the [sys] and [INFORMATION_SCHEMA] related tables & views

I've also tried:

DENY SELECT, VIEW DEFINITION ON SCHEMA::[sys] to [user];
GO
DENY SELECT, VIEW DEFINITION ON SCHEMA::[INFORMATION_SCHEMA] to [user];
GO

This also completed successfully but it took no effect as well.

How can I restrict specific user to a specific schema only without [sys] and [INFORMATION_SCHEMA] listings?

Note/Update: The DENY VIEW SERVER STATE TO [user]; returns:

Msg 40520, Level 16, State 1, Line 1
Securable class 'server' not supported in this version of SQL Server.

Answer 1

I didn't have an install of SQL 2000 handy, but working off of a modern version of SSMS and a local SQL Server linked to an Azure SQL DB I was able to see some things which may shed some light:

First off: SQL Server in Azure mimics as closely as possible the behaviors of the on premise versions of SQL Server. It appears that the catalog of views that you showed is available for the permission level of the login we created; I may not have reproduced the exact circumstances, but I was able to see the existence of system views when my permissions should, in theory, have been restricting them from my view. This seems to go against the spirit of the concept of permissions.

However, this is not unprecedented:

SELECT * FROM sys.databases

Will return master & the current database. (Try it with your restricted permissions- it should still succeed, even though it's technically a sys view).

Yet, a similar query

SELECT * FROM sys.objects 

Throws an expected error (... The SELECT permission was denied on the object 'objects', database 'mssqlsystemresource', schema 'sys'.).

Now, if you're able to select from any/all views listed, this is an entirely different behavior and contradicts the permission entirely.

This was also examined partially in this related question , this one involving sys views specifically, and has a bit of documentation on MSDN too.

EDIT: Here's one more on the INFORMATION_SCHEMA specifically