Deny access to information schema in SQL Server 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.
You should be able to just deny permissions on the entire sys and information_schema schema as a whole:

DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]


That should basically just prevent that user from doing any selects in those two schemas.