HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Deny access to information schema in SQL Server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqldenyaccessserverschemainformation

Problem

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, D

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#25667, answer score: 11

Revisions (0)

No revisions yet.