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

2 Answers 2

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

Firstly, you are correct in that the (slightly counter-intuitive) way to prevent access to the [sys] and [INFORMATION_SCHEMA] schemas is to first ensure that the login (well, server-level principal) exists as a user (erm, database-level principal) in the master database.

Assume you have a SQL login for simplicity:

CREATE LOGIN [testy] WITH PASSWORD=N'SCoBIqlJELGzrY9zYsKWC5z3kHtMsyCAP6yBHLUYQ0w='
go

Now create a corresponding user in the master database:

use [master]
go
CREATE USER [testy] FOR LOGIN [testy]
go

Now you want to prevent this login from accessing any of the tables in the system-provided schemas - [sys] and [INFORMATION_SCHEMA].

It appears there was a behaviour change between SQL Server 2008 R2 and SQL Server 2012:

In SQL Server 2012 (and presumably later versions), running the following in the [master] database does as you would expect:

DENY SELECT, VIEW DEFINITION ON SCHEMA::[sys] to [testy];
GO
DENY SELECT, VIEW DEFINITION ON SCHEMA::[INFORMATION_SCHEMA] to [testy];
GO

However, in SQL Server 2008 R2 (and presumably earlier versions), the stock grant statements giving access on objects in these schemas to members of [public] seem to override the above DENY statements, which seems like a huge pile of fail to me. Consequently on 2008 R2 you need to explicitly DENY for each GRANT to [public]. Here's a script to do that:

declare
    @database_principal sysname,
    @cur cursor,
    @sql nvarchar( 4000 );

set @database_principal = 'testy';

set @cur = cursor local forward_only static for
    select 
        'DENY ' +
        permission_name + ' on ' +
        case class 
            when 1 then
                case minor_id
                    when 0 then 'OBJECT'
                    else 'COLUMN'
                end
            else
                class_desc
        end + '::' +
        case class
            when 0 then db_name()
            when 1 then quotename( OBJECT_SCHEMA_NAME(major_id) ) + '.' + quotename( object_name( major_id ) ) + case minor_id when 0 then '' else ( select '.' + quotename( name ) collate database_default from sys.columns where column_id=minor_id) end
            when 3 then schema_name( major_id )
        end + ' to ' +
        quotename( @database_principal )
    from
        sys.database_permissions
    where
        [grantee_principal_id] = 0 -- public
        and
        [state_desc] = 'GRANT'
        and
        [permission_name] = 'SELECT'
;

open @cur;

while
    1 = 1
begin
    fetch @cur into @sql;
    if @@fetch_status <> 0 break;

    print @sql;
    exec sys.sp_executesql @sql;
end;

close @cur;

deallocate @cur;

Run the above in the master database and you've removed access to the contents of those schemas.

Notes:

  1. Because these are explicit DENY statements, they are correct at the point the script is run. If someone subsequently alters the permissions granted to public (e.g. a service pack creates a new system table) then that will be exposed to the denied user
  2. It's a good idea to use a database role as the target of the DENY statements and to put the denied users in that role.
  3. You can undo this by changing the DENY to a REVOKE
  4. If you comment out the following two lines in the above script:

        and
        [permission_name] = 'SELECT'
    

    It will have the effect of undoing ALL of the default GRANTs for public. This will prevent access to e.g., sys.sp_tables and so break e.g. Microsoft Access's ability enumerate the tables at all, but it is useful in high-security scenarios to do just this so the user(s) get access only where you have explicitly granted it.

share|improve this answer

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.