I am looking for the best way to disable access to the
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
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
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
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.
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.