Security on a Network

[hansnyc]

I'm making a version of my dba using the upsizing wizard, then i'm packinging it to distribute it as a runtime version and save the data on sql server

But i'd like to be able to keep track of who is adding what to the database.
How can I check who added what data? from which station? how can I monitor things in that setting?

Also if 2 users open the same form I'd like to restrict the second user, and have a window poping up telling him/her "you cannot add data to this table until user#123 is done with that table" a little bit like quickbooks does.

 

[Dennisk]

If you are still retaining Access FE then using workgroup protection will still allow you to obtain the current user using the CurrentUer() function.

If you do not have workgroup protection and each individual has a unique network logon name then you can use the Windows Environment variable to obtain the logon name.

if you want to monitor how many users have a form opened and assuming you can identify each user. Then create a table holding the username and formname. Use a switchboard style menu button to open each form for each user then using the table you can determine what functionality you can assign to each user/form

 

[tehNellie]

But i'd like to be able to keep track of who is adding what to the database.
How can I check who added what data? from which station? how can I monitor things in that setting?

implement individual user logins on the server rather than having every instance of your front end connect with a generic login. If all your users are connecting from within a Windows/Active Directory Domain you can implement a trusted connection method that will allow the domain to auth them into your server and then apply user level permissions on the database itself. You could then use a simple audit facility using, for example, triggers to track which changes are being made to your data and by which user.

Also if 2 users open the same form I'd like to restrict the second user, and have a window poping up telling him/her "you cannot add data to this table until user#123 is done with that table" a little bit like quickbooks does.

SQL server can lock to row level rather than the entire table, so you might be un-necessarily restricting your users here. Also given that the lock should only exist while the update is taking place, do you really want to restrict your users from an entire table because fred has gone out to lunch and left a screen open on that table?

 

[hansnyc]

Ok I wont do the restrict thing, but how do I implement individual user logins on the server?
All my users are connecting from within a windows domain
I dont know much about permissions, audit facility and security

 

[tehNellie]

it's not something that can be easily described in a single forum post, but there are some overviews regarding SQL server security:

http://vyaskn.tripod.com/sql_server_security_best_practices.htm (2000)
http://www.microsoft.com/sql/technologies/security/default.mspx (2005)

In a nutshell you can, for example, grant an NT/AD group permission to your server and databse and the function SUSER_SNAME() on SQL server will return the login name of the current user.

It is also possible to assign individual users permission to the server/database and as with a Domain, permissions should be assigned to a group rather than individual users.