Avatar of MageDribble
MageDribble asked on

How do I hide system tables from Access/Excel/Hyperion?

My users have access to a database but when they try to link in the tables, they get ALL system tables as well.  This poses problems when they try to link in tables.  Anyone know how to prevent them from seeing system tables?
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Ted Bouskill

8/22/2022 - Mon
rickchild

- In Access and Excel I believe you can flag the tables as hidden objects.

- Never tried this but if you hide in Management Studio they may be hidden in 3rd Party:
Tools / Options / Environment / General and choose "Hide system objects in Object Explorer."

- See this article, it describes how to add the users to a Role and deny access to system db to that role:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2755503&SiteID=17
Ted Bouskill

How did you configure access to the SQL database?

You should have given them explicit access ONLY to non system tables.  If you setup their SQL access accounts as having full read/write access then you cannot prevent them from accessing system tables.
ASKER
MageDribble

rick - I checked but that solution didn't work

ted - I set them up with GRANT access only to user tables and DENY access to the system views but they still appear in Access & Excel
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ted Bouskill

Well as long as they can't read data or change data in the system tables then you don't have other choices.  It's like shared folders on a file server.  You can see all of them even though you can't access some.  Personally I don't like that behavior but it seems to be the way Microsoft builds products.
ASKER
MageDribble

So there is no way to hide the system tables?
ASKER CERTIFIED SOLUTION
Ted Bouskill

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question