Best way to set up security in SQL Server with Access front end ??

[hblabonte]

I'm new to programming in SQL Server and Access. Forgive me if this seems to be a "simple" topic. I've developed a database in SQL Server (back end) using Access 2000 as the front end.

I'd like to establish all security roles in SQL, and code checks in the Access side. What's the best way to go about this?

Logically, I'd like to establish a modal popup form (Access) to capture the users login data which would allow entry in to particular forms depending on user's role. What exactly do I do in SQL Server? Should I establish a separate "security" table defining user IDs equating them to a particular role? Where do I specify what tables the role is allowed to view/edit?

Any suggestions would be appreciated.

 

[tlbroadbent]

Let SQL Server handle the security. SQL has tables for logins, users, permissions, etc. All the security is built-in. I suggest you read about SQL security in SQL Books Online (BOL).

You'll want to create logins, database roles (optional), and database users. You can assign users to predefined or user created roles. You will need to grant permissions on tables, views and stored procedures to the roles and/or users. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[hblabonte]

Thanks for the suggestion Terry. Could you point me to the "SQL Books Online (BOL)" so that I can read up on this?

thanks again,
Heather

 

[tlbroadbent]

See faq183-689. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.