Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Oct 9 '12 at 10:37

This question came from our site for professional and enthusiast programmers.

    
META: sorry I didn't knew about the dba site, as I not often user stackoverflow. I voted to close and move there too now. Will the question automatically be transfered to that site when enough people vote for it? Or should I open a new one there and close it here? –  SwissCoder Oct 9 '12 at 6:47
1  
The question will be moved automatically, as soon as five users have voted to move it there –  marc_s Oct 9 '12 at 6:53
    
thanks, and greetings back to Switzerland ;) –  SwissCoder Oct 9 '12 at 6:55
1  
I'm not sure that a correct ANSI SQL implementation is allowed to not allow the user to query INFORMATION_SCHEMA (for those objects that they have permission to) –  Damien_The_Unbeliever Oct 9 '12 at 7:26
1  
Also check out this dba.se question and its answer by Remus Rusanu - sort of covers the same topic –  marc_s Oct 9 '12 at 15:00

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
1  
great! adding the "SCHEMA::" helped –  SwissCoder Oct 9 '12 at 7:09
    
@SwissCoder: yeah, sorry - I just tried this here at the office and realized that was missing.... –  marc_s Oct 9 '12 at 7:09
    
I actually helped to create securables, but not the correct ones that would block the user from having access. –  SwissCoder Oct 9 '12 at 7:42
    
marked as answer, but the thing is the SCHEMA:: didn't help, better remove it again. The solution is to add the user to the master database and then run the deny script on the master. Thank you for your help! –  SwissCoder Oct 9 '12 at 7:57
    
Oh and actually I also is not possible to disable the entire SCHEMA, only access to single tables can be disabled like this. –  SwissCoder Oct 9 '12 at 8:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.