How to hide INFORMATION_SCHEMA and sys from users

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Blakey - 14 Jun 2006 18:55 GMT

There's a system procedure that Crystal Reports (for example) invokes upon
connection to a SQL Server database: sp_schemata_rowset

In SQL 2005 this returns the three default Schemas:
dbo
INFORMATION_SCHEMA
sys

I'm trying to suppress/hide the last two Schemas but am not having any luck.
Is it possible to not expose Schemas to users in SQL 2005 (their existence
at all)? Revoke and Deny of various permissions does not seem to be working
(including View Definition which I thought would do it)...

TIA

Signature

Bill Blakey

Reply to this Message

hongju - 15 Jun 2006 00:51 GMT

You can deny a permission using VIEW DEFINITION clause.
Users dont view that in Management studio.

"Bill Blakey"님이 작성한 내용:

> There's a system procedure that Crystal Reports (for example) invokes upon
> connection to a SQL Server database: sp_schemata_rowset
[quoted text clipped - 10 lines]
>
> TIA

Reply to this Message

christophe.leroquais@gmail.com - 15 Jun 2006 08:27 GMT

hongju:

I've tried:

DENY VIEW DEVINITION TO 'myUserName'

at the database level but it is without any effect.
The user still can see all the dbo, INFORMATION_SCHEMA and sys objects

hongju a crit:

> You can deny a permission using VIEW DEFINITION clause.
> Users dont view that in Management studio.
[quoted text clipped - 15 lines]
> >
> > TIA

Reply to this Message

hongju - 16 Jun 2006 03:48 GMT

Yes, see but non select values.
I said that dont view serveral user objects.
Dont support to hide permission on system objects, but not result.

"christophe.leroquais@gmail.com"님이 작성한 내용:

> hongju:
>
[quoted text clipped - 26 lines]
> > >
> > > TIA

Reply to this Message

Todd C - 24 Aug 2006 18:51 GMT

Dear Hongju:

Sorry, but your answer does not make sense. I too am having the same issue:
Using an ODBC connection, users can see all system objects in the
INFORMATION_SCHEMA, and sys schemas. I have tried DENY permissions on View
Definition at all scope levels but still the users can see all these views
using ODBC.

What am I missing?

Can you elaborate on your answer?
Does anyone else have any suggestions?

Thanks in advance.

Todd C

> Yes, see but non select values.
> I said that dont view serveral user objects.
[quoted text clipped - 32 lines]
> > > >
> > > > TIA

Reply to this Message