CODEWITHSUNDEEP
t-sqlazure-sql-database
Milan
Milan

Reputation: 3335

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

I've performed the following:

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

enter image description here

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.

Upvotes: 2

Views: 1215

Answers (1)

Dan Rediske
Dan Rediske

Reputation: 852

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

Upvotes: 1

Related Questions