Obtaining Window login name from a trigger

Hi,
We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know ho
w I accomphish this task.
Try SUSER_SNAME function:
http://msdn.microsoft.com/library/de...u-sus_86xx.asp
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
"Herb" wrote:

> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know
how I accomphish this task.
|||Hi,
Thanks for your suggestion. When I issue SUSER_SNAME while using Window Authication, I do get the Window user ID. However, when I issue SUSER_SNAME while using SQL login, I get the SQL login(for ex. SQL login = RUSER, SUSER_SNAME retrieves RUSER).
"Narayana Vyas Kondreddi" wrote:

> Try SUSER_SNAME function:
> http://msdn.microsoft.com/library/de...u-sus_86xx.asp
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||You'll need to pass the "real" login name through to SQL. One technique for
this when an application uses a single service account is to use SET
CONTEXT_INFO to pass the information through with the user connection and
pull this out in your trigger (i. Note that you have to explicitly set this
in your app for each connection that you require this information for. In
some configurations this may not be practical/possible. See BOL for more on
the command.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||Thanks for the suggestion. I will give that a try.
"Jasper Smith" wrote:

> You'll need to pass the "real" login name through to SQL. One technique for
> this when an application uses a single service account is to use SET
> CONTEXT_INFO to pass the information through with the user connection and
> pull this out in your trigger (i. Note that you have to explicitly set this
> in your app for each connection that you require this information for. In
> some configurations this may not be practical/possible. See BOL for more on
> the command.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||Thanks for that information. We still have some apps that are using COM+
Herb
"Shawn Brock" wrote:
[vbcol=seagreen]
> If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
> "Herb" wrote:
w how I accomphish this task.