Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Hide all system views/tables from users in SQL server 2005 Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 5:49 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:46 PM
Points: 284, Visits: 263
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
Posted Monday, March 9, 2009 12:30 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:22 PM
Points: 942, Visits: 1,062
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.


How to ask for help .. Read Best Practices here.
Post #671862
Posted Tuesday, March 10, 2009 4:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 2,858, Visits: 3,180
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.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,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
Posted Friday, November 20, 2009 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, November 20, 2009 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:29 AM
Points: 9, Visits: 147
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
Posted Tuesday, November 24, 2009 4:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 2,858, Visits: 3,180
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]?


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,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
Posted Tuesday, November 24, 2009 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, November 24, 2009 4:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 2,858, Visits: 3,180
I misunderstood what you wanted to do. I'll try to think of a way to hide the system tables.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,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
Posted Tuesday, November 24, 2009 4:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

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

Ok thanks
Post #823767
Posted Wednesday, November 25, 2009 4:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 2,858, Visits: 3,180
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.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,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 »

Add to briefcase 12»»

Permissions Expand / Collapse