Security Levels with VB6 and SQL Server 2000

[MichaelaLee]

Hi Everyone,
I was just asked to change an app I was in the middle of coding, to support multiple security levels. They would like to set these levels to restrick users from opening some screens. For example the security desk will only see the Check-in form, another group will see more screens, and yet another group will see all screens.
Right now we have SQL Server Standard Edition version 8.00.760using "Windows authentication" and I beleive I may have to change that to work with the multi level security, Right? We are a small shop (I'm only programmer) and I'm also responsable for the admin of the server. I'm new to this side of the fence, but wanting to learn more.
Anyways, What would you all recommend in this case? Are there any good articals on the subject using VB and SQL Server. THanks for any advice.
Michael Lee

 

[avanderlaan]

Could you encode a small security table within the database to map user ids and allowed access? Since you are using Windows authentication you can get the logon user from an API call and administer access control centrally at the server.

 

[MichaelaLee]

Hi Avanderiaan,
THanks for the reply. I can do this. But what would be the best way to "Administer access control centrally at the server". Do you mean setting up users in SQL Server and passing the Windows user ID to SQL Server when the user logs in? Thanks again for the help.
Michael Lee

 

[avanderlaan]

I was thinking of something simple where a row in this mythical table contains both the user id and screen id(s) that identifies the screen(s) that the user can access. A query against this table returns the list of screens that a user can see. The screen that does inserts/deletes on this table can itself be controlled from an original seed row which identifies a power user for the application. That user can add /delete other users at will.