At 8/8/2001 10:12 AM (Wednesday), Damian Dowling wrote:

>The problem is that they cannot easily log user activity, because the
>server thinks the one user is doing everything. Also the Database owner
>username and password is used everytime the program runs and is held in a
>encoded ini file on the client.
>
>They planned to fix the problems by having the users login to the server
>properly, but how to update the user permissions on the other files while
>still keeping the as much of the main program unaltered.
>
>What i was thinking was that triggers would be added to the user and
>permission tables in the Control DB that use UDFs to update the permissions
>on the other files?
>
>Anybody got a better idea? (hopefully one that keeps most of the work on
>the server).

Now this is a whole different kettle of fish altogether!

Sounds like you need to update the user's GRANT and REVOKE status on a set
of databases based on their permissions which are maintained in a control
database. This does not sound like a very dynamic situation so it should
not require triggers.

My suggestion would be to write a separate application which maintains user
GRANT/REVOKE permissions in the control database and then propagates these
to the other database. When a new user is added to the database do it
through this application.

P.S. Stop thinking about using UDFs for this. It will not work well, if at
all! Your idea that it will is an illusion.

Hope this helps!