using (windows authentication) and SQL Server 2000(windows
authentication) on the same machine.
I would like to add two columns onto several tables and
have a timestamp and username inserted into them when a
user performs an update or insert. I believe it's a good
idea to use an insert or update trigger, but I'm not sure
how the asp application delegates who is logged to sql
server.
What is the best way to do this?
Do you need to have IIS and Sql server configured a
certain way in order to grab the username from the asp
application?Front-end code typically has to influence on a trigger. The trigger fires
as a result of the triggering action - INSERT, UPDATE or DELETE. Here's an
example to do what you want:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0
return
update MyTable
set
LastModBy = CURRENT_USER
, LastUpdateDateTime = CURRENT_TIMESTAMP
where
PK in (select PK from inserted)
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Michelle" <michelle.vanden@.eglin.af.mil> wrote in message
news:034501c3cdc5$1a2f3a60$a401280a@.phx.gbl...
The current environment is an ASP frontend with IIS 5.0
using (windows authentication) and SQL Server 2000(windows
authentication) on the same machine.
I would like to add two columns onto several tables and
have a timestamp and username inserted into them when a
user performs an update or insert. I believe it's a good
idea to use an insert or update trigger, but I'm not sure
how the asp application delegates who is logged to sql
server.
What is the best way to do this?
Do you need to have IIS and Sql server configured a
certain way in order to grab the username from the asp
application?|||Hi Michelle,
Thanks for your post. According to your description, I understand that you
want to record and return the current login username to certain table in
SQL Server, when you performed insert or update action. If I have
misunderstood, please feel free to let me know.
Before we go any further, I would like to collect more information from
you: 1. Which username do you want to record, the usernames used to log on
IIS or SQL Server?
2. Which authentication do you choose to log on IIS?
So far as I know, if we want to record the usernames used for SQL Server,
we can try to use suser_sname() to return the string of the current login
identification name
For more information regarding suser_sname function, please refer to the
following article on SQL Server Books Online.
Topic: "SUSER_SNAME"
On the SQL Server side, it seems hard to record the usernames which are
used to log on IIS.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||1). Both IIS and SQL Server if possible. It would be
better if we get the username from IIS and have it
delegated to SQL Server.
2). Basic Authentication w/SSL on IIS on one machine and
windows Authentication w/SSL on another.
quote:
>--Original Message--
>Hi Michelle,
>Thanks for your post. According to your description, I
understand that you
quote:
>want to record and return the current login username to
certain table in
quote:
>SQL Server, when you performed insert or update action.
If I have
quote:
>misunderstood, please feel free to let me know.
>Before we go any further, I would like to collect more
information from
quote:
>you: 1. Which username do you want to record, the
usernames used to log on
quote:
>IIS or SQL Server?
>2. Which authentication do you choose to log on IIS?
>So far as I know, if we want to record the usernames used
for SQL Server,
quote:
>we can try to use suser_sname() to return the string of
the current login
quote:
>identification name
>For more information regarding suser_sname function,
please refer to the
quote:
>following article on SQL Server Books Online.
>Topic: "SUSER_SNAME"
>On the SQL Server side, it seems hard to record the
usernames which are
quote:
>used to log on IIS.
>Thanks for using MSDN newsgroup.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
quote:|||Hi Michelle,
>
>.
>
Thanks for your feedback. In this case, as IIS and SQL Server are on the
same machine, a user's credentials (username:password) will be used to
login to SQL Server after that user has logged into IIS using Basic
authentication.
We are able to record the login information (current login username and
timestamp) for SQL Server using the trigger and the related functions
(SUSER_SNAME, GETDATE() etc.). However, it seems impossible to monitor the
logins to IIS from the SQL Server side. SQL Server is unable to be used to
monitor the logins to IIS.
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michelle,
How is this issue going on your side? Based on my further research, it
seems possible to monitor and record the logins to IIS via ASP programming.
To obtain the detailed information regarding monitoring the logins to IIS
using ASP, it is best that you can post in the ASP newsgroup, such as
microsoft.public.inetserver.asp.general,
microsoft.public.inetserver.asp.db. The ASP newsgroup is primarily for
issues involving ASP programming. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other partners who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us.
Thanks for using Microsoft newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
