Get Windows NT/XP Logon ID in SQL Server

Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
user logs on as "sa"?
SUSER_SNAME () returns "sa"
Thanks
RonRon
What did you mean by Logon ID ? A process id ?
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1144536622.162777.312290@.u72g2000cwu.googlegroups.com...
> Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
> user logs on as "sa"?
> SUSER_SNAME () returns "sa"
> Thanks
> Ron
>|||RonL (sal_paradise_93@.yahoo.com) writes:
> Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
> user logs on as "sa"?
> SUSER_SNAME () returns "sa"
No. And keep in mind that there may not even be a login ID. The user
could log in from a Unix box.
This a good argument why you should run with Windows Authentication only,
or at least why you should do something like:
DECLARE @.pw char(36)
SELECT @.pw = convert(char(36), newid())
PRINT @.pw
EXEC sp_password @.pw, NULL, sa
Run this script, and without looking at the output, print it, and lock it
into a safe.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm trying to pick up the Windows Logon Id in a Trigger. I can retrieve
the Windows Logon Id in code ASP.NET or Windows but I'm trying to
retrieve it in a Trigger.
I've seen code that at connect time concatenates the UserId with the
Computer Name to create the Workstation ID.
e.g.,
Server = .. Initial Catalog= .. Workstation ID= computerName + userid
Then to retrieve the Userid they execute sp_who and retrieve the value
from the column HOSTNAME.
Unfortunately Windows Authentication is not an option.
Thanks
Ron|||RonL (sal_paradise_93@.yahoo.com) writes:
> I'm trying to pick up the Windows Logon Id in a Trigger. I can retrieve
> the Windows Logon Id in code ASP.NET or Windows but I'm trying to
> retrieve it in a Trigger.
> I've seen code that at connect time concatenates the UserId with the
> Computer Name to create the Workstation ID.
> e.g.,
> Server = .. Initial Catalog= .. Workstation ID= computerName + userid
> Then to retrieve the Userid they execute sp_who and retrieve the value
> from the column HOSTNAME.
Yes, the client can be co-operative and some agreed connection property
to the information you want to pass. WSID or AppName are the choises
that are closest at hand. SET CONTEXT_INFO is also an option.
Running sp_who to get the hostname is a serious overkill. You can use
the built-in function host_name(). (And app_name() if you use the
application name.)
However, there is no way that SQL Server can get this information on
its own, so if SQL Server cannot trust the client, you are out of luck.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If NT authentication is not an option, use SQL Server authentcation, but
never allow users to connect as SA. Create as many SQL user roles as needed
then create as many database users and put them in appropriate roles. Only
give the users the privileges they need to execute individual business tasks
- why would they need the power to drop/create/alter SQL objects etc.?
ML
http://milambda.blogspot.com/