How to Disable Editing SQL Server Linked Recordsets and Encrypt it?

[accesser2003]

I am developing a system using the MS Access 2003 tool. The backend is SQL Server MSDE2000.

I want:

- Disable the user from directly editing the SQL Server Linked tables objects in the database window if he open these tables in the Datasheet view. He should edit it only through the MS Access forms.

- When the user opens these linked tables objects in the Datasheet view, I want him to see only the encrypted data as # symbols instead of seeing the real data.

Thanks

 

[Pat Hartman]

You will need to protect the tables at the database level. Your form can log on to the server with a secure userid and password that allows the form full access to the data but unless the user knows the hidden credentials, he won't be able to access the data.

 

[accesser2003]

So What should I do???
If any one can advise

 

[Pat Hartman]

Do you know how to assign passwords in SQL Server? I don't but you need to start with that. So open Enterprise Manager (2000) or SQL Server Management Studio (2005) and read the help files. There are also books on line at the MS site.
Do you know how to set up security in Access - search for the FAQ in the knowledge base. Make sure you have at least two safe backups before starting anything. Then follow the directions in the FAQ EXACTLY.

 

[Banana]

Pat, would it also work to just create a User Level Security but only grant the default Admin user full permission to forms only while denying all permissions for any other objects, thus partially securing the database without requiring a logon for the front end itself?

 

[Pat Hartman]

I think for this to work all queries would need to use the "With Owner Access" option so every query would need to be a stored querydef and the Owner of the querydef would need full rights. It would not be possible to use embedded SQL strings as RecordSources or create dynamic SQL strings in code since they are not objects and therefore don't have Owners.

But, I was actually looking at the wider picture. If the be is SQL server, Jet doesn't really have control over it except for within the database that has ULS defined. You could create a new, unsecured database and link to the SQL server tables. To prevent that, you would need to secure the SQL server database.

 

[Banana]

Thanks for that information about QueryDefs- that would be an important consideration.

You're right about that security of SQL server has to be done within that environment, and not by Jet's mechanism. I had implicitly assumed it was already secured, and he just wanted to prevent linked access to the tables using the same credential for the front-end to log in.