Hide all system views/tables from users in SQL server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Hide all system views/tables from users in...

18 posts, Page 1 of 2

1 2 »»

Hide all system views/tables from users in SQL server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

sivaprasad

Posted Monday, March 09, 2009 5:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, November 09, 2012 6:06 PM
Points: 284, Visits: 260

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 no luck.
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.

Please advise is there any way of doing it

Sivaprasad S - [ SIVA ]http://sivasql.blogspot.com/

Post #671366

Mohit K. Gupta

Posted Monday, March 09, 2009 12:30 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041

In access they must have their options turned on to show system objects.

If you go to Tools Menu -> Options -> General Tab..

There are checkboxes for "System Objects" and "Hidden Objects" if you unselect those then these tables will now show up when you try to link to them via ODBC.

Thanks.

Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth

How to ask for help .. Read Best Practices here.

Post #671862

EdVassie

Posted Tuesday, March 10, 2009 4:06 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

See the post from Piotr Rodak at http://www.sqlservercentral.com/Forums/Topic541937-146-1.aspx

I was going to write something similar, but Piotr has said just about all you need. Work out what users you want to deny access to system tables to and put them into a Windows group. Then within SQL Server, apply 'Deny View and Database', etc to this group using the Permissions tab of SSMS.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #672254

k.van.daalen

Posted Friday, November 20, 2009 9:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 5:27 AM
Points: 3, Visits: 42

I've read the article but still no luck.

Could you elaborate on how to do this using simple examples.

After creating a new role and add the deny view definition to it like this on a database level:

create role [no_schema_view_role]
go
--deny schema access to members of this role
deny view definition to no_schema_view_role
go

The add the role to the user of the database incl the datareader role.

This results in getting al the sys and INFORMATION_SCHEMA tables and dropping the dbo tables.

Please help

Post #822508

Heinrich-192976

Posted Friday, November 20, 2009 10:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:16 AM
Points: 8, Visits: 121

Hi,
that is exactly what i get too.
The way described is not the right way what i need.
If i deny view definition to a role in a db then the users see no user tables but the sys and INFORMATION-SCHEMA-views.
But the users want to see only the user tables and nothing else. I get no solution for this problem.
Can anybody help
Thank's
Heinrich

Post #822529

EdVassie

Posted Tuesday, November 24, 2009 4:08 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

When I run the script in Piotr Rodak's post at http://www.sqlservercentral.com/Forums/Topic541937-146-1.aspx it works for me. When 'testu' is not a member of [no_schema_view_role] it can see details of user tables, and when 'testu' is a member of that role it can not see details of the user tables.

Please can you test this exact situation can see if it works for you. It it does then that means the functionality you want can work, you just have to make it work in your live situation.

When your users connect to SQL Server, do they use a Windows or a SQL Server login? SQL Server will always try to connect using a Windows login before it trys to use a SQL Server login, so if they can log in using Windows they will do that regardless of if you also supply a SQL Server login. If they have logged in using Windows authentication, is their Windows group or account a member of [no_schema_view_role]?

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #823762

k.van.daalen

Posted Tuesday, November 24, 2009 4:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 5:27 AM
Points: 3, Visits: 42

EdVassie,

I've tested the exact situation by running the script of Piotr. This however gives me, just like you said the functionality to enable/disable the viewing of the user tables.
This is however not the functionality i am after!
I'm looking for a way to hide the system and INFORMATION_SCHEMA tables in my odbc connection dialog. I only want to see the user tables. It is simply confusing for the enduser.

My users connect through an ODBC connection by using windows authentication. The windows user is a member of the no_schema_view_role indeed.
Changing the memberschip toggles the visibility of the user tables.

Is this the scenario you talked about, or is there a misunderstaning?

Thanks.

Post #823764

EdVassie

Posted Tuesday, November 24, 2009 4:24 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

I misunderstood what you wanted to do. I'll try to think of a way to hide the system tables.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #823765

k.van.daalen

Posted Tuesday, November 24, 2009 4:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 5:27 AM
Points: 3, Visits: 42

EdVassie,

Ok thanks

Post #823767

EdVassie

Posted Wednesday, November 25, 2009 4:47 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

One of the fundamental design criteria behind the ODBC standard is the ability to run ad-hoc queries. In order to do this, the ODBC driver needs to access certain system tables. It is therefore impossible to block access to these tables if you are connecting via ODBC - if you were successful in blocking access to the tables then ODBC would no longer work. See http://msdn.microsoft.com/en-us/library/ms712628(VS.85).aspx for details of the Microsoft ODBC driver.

However, some ODBC driver vendors recognise that allowing the end user to see the system catalog views can cause problems, and have an option in the connection string or a registry option to prevent direct access by user SQL to the system tables. If this facility exists in the Microsoft ODBC driver, you may be able to force this option in your MS-Access connection - check the documentation to find more on this.

If all else fails, you can try DENY access to the sysetm tables to the Public role, but this may cause unwanted side effects that stop things you need from working.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #824428

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions