I have recently transfered data from MS Access to MS SQL Server 2000 and created user interface in MS Access Project (ADP). Now I need to secure the data on server and want to access it ONLY through interface I created in MS Access. Every user has to log in the application (user id and password). Users may have the same access rights to the server (so maybe just one account or whatever I need for all of them). Can anyone advice me how to set it up? To access sql server from MS Access I am using ADO.
But be aware that it is better practice to limit your to establish security at the data level than the application level. Establishing Application Roles is no substitute for making sure that your database is locked down.
If it's not practically useful, then it's practically useless.
Yes, I agree. I have no experience with SQL Server. In MS Access it was easy. So, how to lock down database. What I should create there to secure data? Any password? And then I need to access data through ADO. There is connection string where I have to pass a password.