MS SQL: Hiding sys and INFORMATION_SCHEMA Objects

As of SQL 2005, Microsoft created a super-secret hidden database called mssqlsystemresource.  The master database is now really just a front for this database.  So in reality, these objects do not exist in master and as a user, you cannot access the mssqlsystemresource database.

However, there is a work around.  First a disclaimer, middleware such as ODBC rely on access to sub set of these tables, so once you restrict them from the user, you are also restricting them from ODBC (and other middleware).

Here is a simple example:

USE master

CREATE LOGIN SecuredAccount WITH PASSWORD=N'Password123', DEFAULT_DATABASE=Junk,DEFAULT_LANGUAGE=us_english, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE Junk

CREATE USER SecuredAccount FOR LOGIN SecuredAccount

GO

-- Restrict the user from any DDL

EXEC sp_addrolemember N'db_datareader', N'SecuredAccount'

EXEC sp_addrolemember N'db_datawriter', N'SecuredAccount'

GO

--

-- ----------------------+

USE master

CREATE USER SecuredAccount FOR LOGIN SecuredAccount

GO

-- Blocks INFORMATION_SCHEMA Objects

EXEC sp_addrolemember N'db_denydatawriter', N'SecuredAccount'

GO

EXEC sp_addrolemember N'db_denydatareader', N'SecuredAccount'

GO

--  Each individual object in the sys schema needs to be restricted

--  But this can be scripted, so its not too bad.

USE Junk

-- Tables and views can be restricted at the local database

DENY SELECT ON sys.all_objects TO SecuredAccountGO

USE master

-- Stored Procedures need to be blocked in the master database

DENY EXECUTE ON sys.sp_tables TO SecuredAccount

GO

And as a test, created a simple table called and inserted four rows, and then log in as SecuredAccount and execute:

SELECT * FROM Test;

SELECT * FROM INFORMATION_SCHEMA.Tables;

SELECT * FROM sys.all_objects;

EXECUTE sys.sp_tables

The results should be:

-- The first select returns the 4 rows correctly

(4 row(s) affected)

-- Everything else fails. Note the database name in the error messages.

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object 'TABLES', database 'mssqlsystemresource', schema 'INFORMATION_SCHEMA'.

Msg 229, Level 14, State 5, Line 3

The SELECT permission was denied on the object 'all_objects', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Procedure sp_tables, Line 1

The EXECUTE permission was denied on the object 'sp_tables', database 'mssqlsystemresource', schema 'sys'.