Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Terpfan
Starting Member
4 Posts |
 Posted - 2008-03-07 : 11:42:07
|
| How can I hide the system tables (sys and INFORMATION_SCHEMA) from users? I am not talking about Management Studio. We have users who access the database with ODBC and I do not want them to see all these system tables. I have created a Hide_System_Tables role and added users to it. I have tried denying all the permissions to this role, but they still see it. Oddly enough, in the INFORMATION_SCHEMA permissions, it shows access from grantors of INFORMATION_SCHEMA and dbo. I cannot deny permission for the entries with dbo as grantor.Does anyone have any suggestions on where I have to go to remove all the places that give users access to these tables?Thanks,Mike |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-07 : 12:01:55
|
| Why do you want to do this? What does it matter?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-07 : 22:54:26
|
Having "access" to them is no big deal. Unless of course they are all using an 'sa' login.....Echo tkizer here...why does this matter?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Terpfan
Starting Member
4 Posts |
Posted - 2008-03-10 : 16:55:47
|
| We have users accessing the database with SAS and Crystal and I only want them to see the tables they need and they don't need to see these. We are trying to keep their lives as simple as possible. |
|
|
|
xgirl
Starting Member
6 Posts |
Posted - 2008-03-10 : 17:46:58
|
| One possibility is create a role which denies access to system tables. Assign the users datareader and the role that denies access to the system tables. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-10 : 17:48:29
|
quote:
Originally posted by xgirl
One possibility is create a role which denies access to system tables. Assign the users datareader and the role that denies access to the system tables.
Ummm, he already did that. Check out his first post.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
arcintl
Starting Member
1 Post |
Posted - 2008-03-11 : 17:27:16
|
| i too would like to do this. my reasons are some users are too nosey for their (and mine) own good. also some users have a little odbc knowledge of whats needed to be done but showing all these tables would just blow their minds. even if i say ignore them i know time after time they will come back to annoy me.my users are used to odbc with tables that they can freely browse to get the information they need and i would like this sql server to be no different.Thank you in advance for your help. and hope we all get the answer we are searching for :) (sorry to jump post but i think we are all after the same solution) |
|
|
|
|
|
|
Hide system tables from users?