November 28, 2012 at 2:09 pm
SQL Server DB has 100 tables and 30 views. The typical Access DB just connects (links) to all of them. A User with a UN/PW connects via odbc.
Now the GIS groups wants to use the data, read-only, from 3 views.
The GIS group should not have the ability to link to all 100 tables and 30 views.
Objective: When the GISData user connects with the ODBC Linked tables - GISData should only have permission to Select (read-only) for the three specified views.
Created a USER - GISData. Grant Permission to connect to the DB engine
Server Roll - Public User Mapping: DBName, GISData, dbo (default schema)
Created a view vGISWell
Run TSQL: GRANT SELECT ON [vGISWell] TO [GISData]
Under vGISWell Property - Permissions GISData (user) check Select [x]Grant [x]With Grant
Problem: On my Linked tables for ODBC - it list all 100 tables and 30 views. They are all read-only.
But, I only want it to display (or be able to connect to) the vGISWell (and two others).
All tables, views can be connected (as read only).
What should I do?
November 28, 2012 at 3:28 pm
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
Link to list of system tables shown:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=45325&d=1354141123
Would rather not show all the system tables.
December 4, 2012 at 1:16 am
create new login ,
grant persmission on particular view those they want .
once permission granted ,after that connect sql server with new login ,
exec query sp_helprotect ,
u will get views and tables accessible to him.
December 4, 2012 at 2:11 pm
Why give them the same Front End Access app? Recreate the app, deleting all the linked tables except the three views. They only see the linked tables on Access, not the entire SQL Server database....
Or, write a little code on the main Access app that "Hides" all the tables and queries except those three.
I assume your Access app is run from the user's client computer. In that case, you can easily have an autoexec macro that deletes or hides all the tables and queries from the Access app (remember, they're not actual tables, they just point to the SQL Server tables and views...) if the user is one of your GIS folks. Since the app is resident on his computer, no one else will be affected.
See Tony Teows' Auto FE Updater autofeupdater.com for some sage advice on the topic.
Jim
December 4, 2012 at 2:34 pm
My Access apps use DSNLess connections using SQL Native Client 10.0 that are code / table driven. So, users don't have the exposure of using ODBC. The Access Application is distributed via Citrix so the users do not have any Access menu available.
This question is about a SQL view that is made available to an outside group (GIS mapping).
I don't want the GIS mapping group to view the entire database where some of the tables have confidential information.
Three read/only views were established for the GIS mapping group.
Now the GIS mapping group has a un/pw that allows them to use the GIS software's built-in ODBC tool to link to only the three views.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply