YogiMySonMySon
asked on
Accessing User Login Information From A Trigger (plus other questions)
Hello All,
This is what I am trying to end up with. I am having some problems with the things mentioned on the second paragraph
When the web site user logs onto the site a connection is initially made to the database (with an sql login that has limited access to a stored procedure only) and then the stored procedure, eg 'logonUser' is called and this receives the persons logon information(ID and Password), looks up the existing security schema we have to find what data this person has access to and returns the details of the sql role that the site should re-connect to the DB with.
When the user makes changes to data, there needs to be an audit trail and part of the audit trail is a field that specifies the ID of the person who logged on. Currently there are triggers on these tables but the trigger needs to be able to know who is the person that's making the change. What options do we have? One idea was to save the users id in a ##global temporary tables?? We also need to be sure that any objects or tables made are not floating around and are closed properly. The temporary table would have to be dropped when the person logs off, but what if the user just closes the browser or there is no chance for a logoff script to run completely? What is the session timeout? I also read up on CONTEXT_INFO but I do not know if we can use this. It apparently uses the process ID. Is the process ID still unique regardless of wether connection pooling is on or not?
(SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID)
THANK YOU.
>(:0)-><
This is what I am trying to end up with. I am having some problems with the things mentioned on the second paragraph
When the web site user logs onto the site a connection is initially made to the database (with an sql login that has limited access to a stored procedure only) and then the stored procedure, eg 'logonUser' is called and this receives the persons logon information(ID and Password), looks up the existing security schema we have to find what data this person has access to and returns the details of the sql role that the site should re-connect to the DB with.
When the user makes changes to data, there needs to be an audit trail and part of the audit trail is a field that specifies the ID of the person who logged on. Currently there are triggers on these tables but the trigger needs to be able to know who is the person that's making the change. What options do we have? One idea was to save the users id in a ##global temporary tables?? We also need to be sure that any objects or tables made are not floating around and are closed properly. The temporary table would have to be dropped when the person logs off, but what if the user just closes the browser or there is no chance for a logoff script to run completely? What is the session timeout? I also read up on CONTEXT_INFO but I do not know if we can use this. It apparently uses the process ID. Is the process ID still unique regardless of wether connection pooling is on or not?
(SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID)
THANK YOU.
>(:0)-><
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi All,
(There was a bit of a delay for this comment because I'm in a different time-zone.)
If I use the suggested solution on that link(set the workstation to be the persons username and refer to it in my trigger using host_name) what effect would this have on my connection pooling? because the connection string will be different for each person. Does that mean I'll only be having pooled connections if the SAME person reconnects to the DB and not for each different user? I'm going to be connecting to the DB with user 'roles' not unique logons for each user(ie. all 15000 so users will connect with one of about 50 roles)
[:-)-[]-<
(There was a bit of a delay for this comment because I'm in a different time-zone.)
If I use the suggested solution on that link(set the workstation to be the persons username and refer to it in my trigger using host_name) what effect would this have on my connection pooling? because the connection string will be different for each person. Does that mean I'll only be having pooled connections if the SAME person reconnects to the DB and not for each different user? I'm going to be connecting to the DB with user 'roles' not unique logons for each user(ie. all 15000 so users will connect with one of about 50 roles)
[:-)-[]-<
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that's good
No, the accepted answer was to put the user in the "workstation" and this is available in the trigger--no session variable needed....