Can you retrieve NT username from SQL

[MaffewW]

Hi, I need to try and return the NT username. I've done a keyword search and I dont think theres a way of doing it. I can get the station name, db login etc, we use 3rd party software and everyone uses the same SQL server login so this is no good. Can it be done?

 

[tek2002]

i dont think that you can from SQL Server but you can use ADSI from several other platforms to obtain info about the user object in Win NT Active Directory.

Take a look at the microsoft site - see below for a link that should help get you started.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netdir/adsi/adsgetobject.asp

good luck

 

[MaffewW]

thanks for the link, I really needed it from SQL though as its part of a query

I am fairly sure it cant be done

 

[MikePP]

Hi

It can be done

Lookup System_User in Books online

Sample Stored procedure follows:

/*
Returns the user's current record
*/
CREATE PROCEDURE spUser AS
DECLARE @NTUser varchar(50)
SET @NTUser = RTrim(SubString(System_User,Patindex('%\%',System_User)+1,50))
SELECT Top 1 * from tbUsers WHERE RTrim(fdNTUserID) = @NTUser

Regards

sadcow

 

[tlbroadbent]

MikePP,

The stored procedure works if you have the table tbUsers. However, I don't think most of us have such a table on our servers.

MaffewW,

You are correct about being unable to obtain the NT login name from SQL Server if connected using SQL authentication. You must use NT authentication to obtain the NT Login. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.