MSSQL Session

15 Jul 2005 4:08 AM

Randy Adanza

Cheers,

I need to implement an audit trail on a existing Database. The way im
planning to do this is to make a duplicate table for each of the tables on
the Database and add 2 fields, namely Change Date and Change By. Now on
these audit tables you are only allowed to do an insert, so i figured that
the easiest way to do this is to create a trigger on the main tables
something similar to this:

create trigger trig1 on mytable
for insert, update
as
    insert into mytable_audit (field1, field2, ChangeDate, ChangeBy)
    select
        i.field1, i.field2, getdate(), userid from inserted i

we have a table that holds all the usernames/passwords. Now my problem is
that how can i get the userid of the username that logs in to the server
using my application?   What I am thinking of doing is that I create a new
table lets say UserSessions Table and place there the SessionID of the users
connection to the sql and the userid. Now my question is, is there a way
for me to know the sessionid of each of the connections that my application
has with MSSQL? bacuase
if I can get this session id i could make a sql function that will return
the UserId of the current usersession. If someone has a better idea please
feel free to share.

btw, my application uses only one username/login to log to the server. And
the application will just ask for a username/password from the user which is
stored in a users table.

TIA
Randy.

15 Jul 2005 4:18 AM

David Gugick

Randy Adanza wrote:
Show quote

> Cheers,
>
> I need to implement an audit trail on a existing Database. The way im
> planning to do this is to make a duplicate table for each of the
> tables on the Database and add 2 fields, namely Change Date and
> Change By. Now on these audit tables you are only allowed to do an
> insert, so i figured that the easiest way to do this is to create a
> trigger on the main tables something similar to this:
>
> create trigger trig1 on mytable
> for insert, update
> as
>    insert into mytable_audit (field1, field2, ChangeDate, ChangeBy)
>    select
>        i.field1, i.field2, getdate(), userid from inserted i
>
> we have a table that holds all the usernames/passwords. Now my
> problem is that how can i get the userid of the username that logs in
> to the server using my application?   What I am thinking of doing is
> that I create a new table lets say UserSessions Table and place there
> the SessionID of the users connection to the sql and the userid. Now
> my question is, is there a way for me to know the sessionid of each
> of the connections that my application has with MSSQL? bacuase
> if I can get this session id i could make a sql function that will
> return the UserId of the current usersession. If someone has a
> better idea please feel free to share.
>
> btw, my application uses only one username/login to log to the
> server. And the application will just ask for a username/password
> from the user which is stored in a users table.
>
> TIA
> Randy.

Any particualr reason you are not implementing SQL Server security and
instead managing logins separately? If you were using integrated or SQL
security, you could just use SUSER_SNAME() to return the name of the
current user. What about updates and deletes. Can't the application
perform these DML tasks on a table? You can always access the current
spid using @@SPID, but the sysprocesses system table will only contain
the name of the common user name you are using.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

6 Oct 2005 8:05 PM

scuba79

Randy,

Did you ever get this issue resolved, I'm in the same situation.

Thanks
Scuba79

Show quote

"Randy Adanza" wrote:

> Cheers,
>
> I need to implement an audit trail on a existing Database. The way im
> planning to do this is to make a duplicate table for each of the tables on
> the Database and add 2 fields, namely Change Date and Change By. Now on
> these audit tables you are only allowed to do an insert, so i figured that
> the easiest way to do this is to create a trigger on the main tables
> something similar to this:
>
> create trigger trig1 on mytable
> for insert, update
> as
>     insert into mytable_audit (field1, field2, ChangeDate, ChangeBy)
>     select
>         i.field1, i.field2, getdate(), userid from inserted i
>
> we have a table that holds all the usernames/passwords. Now my problem is
> that how can i get the userid of the username that logs in to the server
> using my application?   What I am thinking of doing is that I create a new
> table lets say UserSessions Table and place there the SessionID of the users
> connection to the sql and the userid. Now my question is, is there a way
> for me to know the sessionid of each of the connections that my application
> has with MSSQL? bacuase
> if I can get this session id i could make a sql function that will return
> the UserId of the current usersession. If someone has a better idea please
> feel free to share.
>
> btw, my application uses only one username/login to log to the server. And
> the application will just ask for a username/password from the user which is
> stored in a users table.
>
> TIA
> Randy.
>
>
>