SQL Server - get domain user full name - Asked By sammy mc on 21-Mar-12 04:26 PM

Hello folks,
though I get windows user name from following statements

SELECT SUSER_SNAME()

SELECT ORIGINAL_LOGIN()

SELECT SYSTEM_USER

SELECT REPLACE(SUSER_NAME(), HOST_NAME() + '\','') as userId

SELECT  loginame FROM sys.sysprocesses WHERE spid = @@SPID


I am looking some solution that brings my full user name from AD in SQL Server...
Pl advise if you have any thoughts...
[)ia6l0 iii replied to sammy mc on 21-Mar-12 09:02 PM
First of all, have you used windows authentication? All these queries, I guess, only return the current session/logged in user name with domain - if you have logged in using windows authentication. 

SUSER_NAME() function itself returns the fully qualified user name with domain. 

If you are unsure about the logins, you should run the EXEC sp_who2 to figure out who have logged in, and then verify if windows authentication is used.



Sandeep Mittal replied to sammy mc on 22-Mar-12 12:45 AM
Check this if works

DECLARE @DOMAIN_NAME VARCHAR(100)
SET  @DOMAIN_NAME = 'domainname' --set domain name here
SELECT  DISTINCT STUFF(loginame,1, LEN(@DOMAIN_NAME)+1, '')
FROM    sys.sysprocesses
WHERE   loginame LIKE @DOMAIN_NAME + '%'
D Company replied to sammy mc on 22-Mar-12 01:05 AM
One way to achieve this is create a linkedserver from SQL and qury against active directory. this article has good examples
http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-t-sql/
here is step by step process from SSMS
http://sql.dzone.com/news/querying-active-directory-thro

First it will create a linkedserver whick linked to the AD, then u can query against the ad

REGARDS
D
[)ia6l0 iii replied to [)ia6l0 iii on 22-Mar-12 11:40 AM
Good one [)ia6l0. Your thinking cap is good.
Anoop S replied to sammy mc on 31-Mar-12 01:27 AM
Try this code

CREATE FUNCTION getNTUser (@spid int)
RETURNS varchar(50) AS
BEGIN
DECLARE @ntUser varchar(50)
select @ntUser = nt_username from master.sysprocesses where spid = @spid
return @ntUser
END

declare @UserName nvarchar(16)

select @UserName = dbo.GetNTUser(@@SPID)