January 18, 2005 at 10:26 am
Hi
I run a SQL Server database which is accessed by an Access 2000 front end.
I need a user to access tables through a separate, read only, Access 2000 front end, purely for reporting purposes. This same user has access to the main Organisation Access 2000 front end and has all rights required by him.
How can I make a set of tables accessed by this user Read Only just for reporting purposes.
Many thanks
Paul
January 18, 2005 at 10:34 am
Give them access to views that are read-only for the reporting application OR force them to use stored-procedures to return data for reporting.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 19, 2005 at 2:53 am
Hi
Many thanks for your reply. I'm sure the the View permissions would work fine. However, I have a lot of tables and was hoping to find an easy way to do this.
I was thinking about copying the database every few minutes using a DTS job. This would provide a set of tables which wouldn't have the same vulnerability issues. However, I am struggling to produce the SQL to do this efficiently.
I would be grateful for any ideas.
Paul
January 19, 2005 at 7:08 am
Paul
You could create a new login that has only SELECT permissions, on only the tables they need for reporting, and use that login to connect to SQL Server from your "separate, read only, Access 2000 front end".
January 19, 2005 at 9:03 am
Hi
That would definitely work, but I am using Window's login rather than SQL login. That means the person will retain his permissions.
Many thanks
Paul
January 19, 2005 at 9:07 am
Is there a reason you don't want to create a SQL login and use that just for the reporting mdb? I'm guessing that you're using ODBC to link Access to the SQL tables.
January 19, 2005 at 9:15 am
That's exactly right.
Many thanks
January 19, 2005 at 9:56 am
Continuing my idea, you would run something like this when the Access reporting mdb opens:
DBEngine.RegisterDatabase "ApplicationName", "SQL Server", True, "Database=MySQLDatabase;id=MyReadOnlyLogin;pwd=MyPassword"
...which creates a DSN named "ApplicationName" pointing at the SQL database named "MySQLDatabase" and the user "MyReadOnlyLogin" has only SELECT permissions etc etc.
January 20, 2005 at 10:51 am
This sounds great.
I have tried it but am probably being a bit thick.
I have a database called PT an a server called Hamlet. I have created a local user on the server called PT_RO with password livingstone5
so:
DBEngine.RegisterDatabase "ApplicationName", "Hamlet", True, "Database=PT;id=PT_RO;pwd=livingstone5"
How should I run this?
Many thanks for your help and your time.
January 20, 2005 at 11:23 am
Your code should actually read like this (2nd argument names the driver to be used):
DBEngine.RegisterDatabase "ApplicationName", "SQL Server", True, "Database=PT;id=PT_RO;pwd=livingstone5"
To test this out, switch to the VBA side (Alt-F11) and press Ctrl-G for the Immediate Window. Copy/paste that statement and press ENTER. Then visit the ODBC admin in Control Panel to verify that the DSN is there and it works. Then:
1. Add a new module, create a new function, and place the RegisterDatabase code in the function.
2. Create (or add to) your AutoExec macro and set the Action to RunCode and use that to run the function you created in #1 above.
Every time you open the mdb it will create (or refresh) the DSN.
January 20, 2005 at 8:40 pm
For MS SQL 2000 I see at lest two ways of doing that:
1 - Put those tables on READ-ONLY filegroup;
2 - Put that login indo db_denydatawriter fixed database role;
Check BOL and see what suits your most.
Igor
January 21, 2005 at 9:28 am
This is terrific. Both work for me.
Thanks very much for all your help - it is greatly appreciated.
Paul
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply