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/

Get user syntax

I am building a view for a report and one of the requirements is to grab the
user who is running the report. What would this syntax look like? I thought
it was getuser(), but no luck there.
Thanks,
Ryan
Ryan,
Depending on whether you want a User or a Login, you can try one of the
following: CURRENT_USER, SYSTEM_USER, USER_NAME, SUSER_SNAME. Check them
out in the Books Online, or just run them and choose what you prefer to use.
RLF
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:266EBEEE-3FB5-448D-A58B-0EF6D8F006F1@.microsoft.com...
>I am building a view for a report and one of the requirements is to grab
>the
> user who is running the report. What would this syntax look like? I
> thought
> it was getuser(), but no luck there.
> Thanks,
> Ryan