Hide all sys and INFORMATION_SCHEMA views from users in SQL server 2005

Question

We have a request from client to hide all system views/tables from users in SQL server 2005.

As user assigned to a specific database role, client do not want the user to see all system tables and INFORMATION_SCHEMA views, so they can have a clear view for only user tables in their schema.

 

However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views.

Also when connecting from SQL Management Studio, they are getting same list.

 

We have taken following steps,but it does not worked.

1.      DENY permissions on View Definition at all scope levels but still the users can see all these views using ODBC.

 

2.      Tried denying access by changing permissions to deny in the public role, but still the same.

 

3.      Created one Role including deny permissions to all sys and INFORMATION_SCHEMA views and assigned to user, but same issue.

 

Any suggestions.

---

Sivaprasad S

Answer 1

We are also having the same  issue.

Is there any solution to the above question or is it a bug in SQL Server 2005 ?

Answer 2

Hi,

check this link
http://social.technet.microsoft.com/Forums/en-US/sqlsecurity/thread/126dee23-38df-4dc0-b072-addc1119285e/
http://www.sqlservercentral.com/Forums/Topic671366-359-1.aspx

I hope these links might be helpful

---

Rajesh Jonnalagadda http://www.ggktech.com

Answer 3

After reading the above information is still have the same problem . Does anybody know how to handle such a situation.

I've tryed to remove the sys and INFORMATION_SCHEMA objects by creating a new role and deny the view definition

create role [no_schema_view_role]

go

--deny schema access to members of this role

deny view definition to no_schema_view_role 

go

No luck..

The dbo tables disappear after excecuting this code and they are the ones that need to remain visible.

please help

Answer 4

Is this a a bug of SQL Server? i also tried the command: "deny view any definition to public", but still can see INFORMATION_SCHEMA and sys objects via ODBC connection.

Please help...

Answer 5

The problem here is that 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 SecuredAccount
GO

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'.

 

---

William F. Kinsley