I am looking for the best way to disable access to the sys.tables
/ Information Schema
for a user / group in SQL Server.
I found this thread from 2008
It shows a way how to deny access on [sys].[something]
like so:
DENY SELECT ON [sys].[columns] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[tables] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelectRole
GO
But no way how to disable access on the Information Schema
:
DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelectRole
This seems not to work.
How can I disable access to information_schema?
And is there an easier way disable access to all sys
/ information_schema
?
Update:
Actually I can not run both ot the following statements:
DENY SELECT ON [sys] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA To reducedDBO
GO
I tried to run them on the specific DB where the User exists, and I also tried on the "master".
I still can run:
SELECT * from
INFORMATION_SCHEMA.TABLES
–>still returns results
SELECT * from
sys.TABLES
–>no results anymore
Including SCHEMA::
in the query made it possible to create the securables
DENY SELECT ON SCHEMA::[sys] TO reducedDBO
GO
DENY SELECT ON SCHEMA::INFORMATION_SCHEMA To reducedDBO
GO
But now I still can select all the information from the DB.
I had a look at the "Securables"-Tab in the users Property-window in Management Studio 2008, it looks like this:
Entry that does block the selecion of sys.tables
Schema:sys, Name:tables, Type:View
Permissions for sys.tables: Permission:Select, Grantor:dbo, Deny is checked
Entry that do not block any selection
Schema:, Name:INFORMATION_SCHEMA, Type:Schema
Permissions for INFORMATION_SCHEMA: Permission:Select, Grantor:dbo, Deny is NOT checked (I tried to check it, but no chance..)
Permission:Select, Grantor:INFORMATION_SCHEMA, Deny is checked
I tried to set the permissions over the GUI, but then I get the same error that setting permissions would be possible only on the master DB.
But I not have the user/login added to the master DBs security.
Solution:
The only way I could make the deny
work for the information_schema
was to add the user to the master-db and run the deny select
on the master:
DENY SELECT ON [sys].[tables] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA.TABLES To reducedDBO
GO
And as in this code, it can only be executed for single tables.
Best Answer
You should be able to just deny permissions on the entire
sys
andinformation_schema
schema as a whole:That should basically just prevent that user from doing any selects in those two schemas.