Access 2003 Project Security Setup

[Patrick Wolf]

Hi,

I am thinking of setting up the security for my current Access 2003 Project
with MS-SQL 2000 in the following way and would like some comments and tips
about it if possible:

- We use SQL Server 2000 with integrated authentication
- The Database itself has a these roles: "user, manager, admin"
- These roles have different rights on the tables in the database
- In the Enterprise Manager we make logins for the windows user groups
"user, manager and admins" and connect them with the internal roles of our
database.
- User log on to their computer.
- User open the Access Project and will automatically log on to the SQL
Server into their respective groups and roles due to integrated
authentication (does this work this way?). Eg. a user in the windows group
"manager" will end up having the role "manager" in the database?
- Inside the Access Project with VBA I like to query SQL Server about which
role the user has and what the user name is (how would I do this)?
- With knowing the user name and role I can go about disabling buttons etc.

Thanks a lot for any help or suggestions
Patrick

 

[Vadim Rapp]

Hello Patrick:
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 29
Apr 2005 21:19:57 +0930:

PW> Hi,

PW> I am thinking of setting up the security for my current Access 2003
PW> Project with MS-SQL 2000 in the following way and would like some
PW> comments and tips about it if possible:

PW> - We use SQL Server 2000 with integrated authentication
PW> - The Database itself has a these roles: "user, manager, admin"
PW> - These roles have different rights on the tables in the database
PW> - In the Enterprise Manager we make logins for the windows user groups
PW> "user, manager and admins" and connect them with the internal roles of
PW> our database.
PW> - User log on to their computer.
PW> - User open the Access Project and will automatically log on to the SQL
PW> Server into their respective groups and roles due to integrated
PW> authentication (does this work this way?). Eg. a user in the windows
PW> group "manager" will end up having the role "manager" in the database?
PW> - Inside the Access Project with VBA I like to query SQL Server about
PW> which role the user has and what the user name is (how would I do
PW> this)? - With knowing the user name and role I can go about disabling
PW> buttons etc.

You can even simplify things by using function is_member to determine if the
user is a member of a particular windows group, and disabling buttons
acordingly. Then you can avoid setting up roles in the database - assuming
that the users would work with the databse only through your application,
and the application is ADE.

Vadim Rapp

 

[Patrick Wolf]

Thanks Vadim. Good idea but I like the database to be secure aswell.

All the best
Patrick