Get User Name from suser_name() and host_name()

Question

Get User Name from suser_name() and host_name()

david.c.holley

SSCrazy

Points: 2423
More actions
May 20, 2010 at 11:56 am

#219185

SELECT REPLACE(SUSER_NAME(), HOST_NAME() + '\','') as userId
I need to pull out the user name from the fully qualified name ([domain name]/). The statement above isn't working as it fails to add the backslash to the host name.
What am I missing?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

david.c.holley (5/20/2010)
SELECT REPLACE(SUSER_NAME(), HOST_NAME() + '\','') as userId
I need to pull out the user name from the fully qualified name ([domain name]/). The statement above isn't working as it fails to add the backslash to the host name.
What am I missing?

The host_name will not be found in the suser_name function. You are looking for the string that matches host_name + \ inside the string of suser_name.

Are you trying to replace the user id with the host_name or with an empty string?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

i think this is what you might be after:

--results:

TheDomainTheUser

TheDomainlowell

declare @MyDomainLogin varchar(30)

SET @MyDomainLogin='TheDomain\lowell'

--SET @MyDomainlogin = SUSER_NAME()

SELECT

--the -1 and +1 are to remov ethe char '\' itself fromt eh results

SUBSTRING(@MyDomainLogin,1, CHARINDEX('\',@MyDomainLogin) -1) As TheDomain,

--find the location of the slash, grab substring of the right of THAT.

SUBSTRING(@MyDomainLogin,CHARINDEX('\',@MyDomainLogin) + 1,30) As TheUser

WHERE CHARINDEX('\',@MyDomainLogin) > 0--don't crash on a non-domain name

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

david.c.holley SSCrazy Points: 2423 More actions · Answer 3

Maybe I should spend more time at work doing work and less time at home doing work. Since I'm running SQLServer Express on my laptop, the server name and host name are the same hence it works.

sqlbi.rahul SSC Enthusiast Points: 140 More actions · Answer 4

try this

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

i hope it works..

i have mine setup as

SELECT REPLACE(SUSER_NAME(), 'Dom-cs\','') as userId

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

sqlbi.rahul (5/20/2010)
try this
SELECT REPLACE(SUSER_NAME(), HOST_NAME() , '\','') as userId
i hope it works..
i have mine setup as
SELECT REPLACE(SUSER_NAME(), 'Dom-cs\','') as userId

That will not work due to too many parameters passed to the replace function.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events