Hide all system views/tables from users in SQL server 2005
-
Hello,
I had the same problems with SQL Server 2008 R2 with ODBC. When connecting to any database all systemviews from db master were shown in addition.
I have done following in the management studio: After selecting db master, properties, I added the role public in rights and revoked "select". That was all!
Did you try this already?
In ODBC-Management "Sql Server native client 10"-driver is a must! With older versions it does not work.
New ODBC-connections can only be created with accounts without "public"-membership!
best regards
Martin
-
Image of what the discussion is about.
Steps to limit a user to choose only the Views they have permission for:
1. In Databases, Security, Logins - New Logins
Login Name: GISviewer (password) turn off password enforce policy
Default DB - RegDB User Mapping - RegDB
2. run tsql on the view GISWell
Use RegDB
GRANT SELECT ON vGISWell TO [GISviewer]
3 Open Access - External Data (Native SQL - add server name)
in data source UserName GISViewer Password: .....
Link Tables Result:
Only the dbo.vGISWell shows on top ' desired!
Plus.... Not Desired
All the Information_Schema.check_constraints
All of the sys.all_xxxx See link image above
Did the solution above Work?
-
http://support.microsoft.com/kb/2513216
Got to love Microsoft SQL Server Support
After dozens of people asking how to solve this - Microsoft referenced this article.
It sure shows the problem. But, it is the End User (all of them) we shoud educate?
Microsoft warns that deny select to public may have unintended effects - but MS offers no real solution.
Keywords: deny view definition to public
Keywords: deny select to public
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply