SUSER_SNAME = username@fulldomain??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SUSER_SNAME = username@fulldomain???

Author

Topic

gthimmes
Starting Member

1 Post

Posted - 2008-08-21 : 10:51:54

We are having trouble with an inconsistency in the format of data returned by calling SUSER_SNAME. What we have always gotten from this call is the username in the format of [Domain]\[Username]. At one of our customer sites, calls to SUSER_SNAME are returning in the format of [Username]@[Fully qualified domain name]. We are suspecting that this may be something different in their Active Directory setup, but it could just as easily be a SQL Server setting. In order for the functionality of our application that relies on this to work correctly, we need usernames to always return from SUSER_SNAME as [Domain]\[Username].
What is causing this change in username format, and what can we do to fix this problem?

Thanks in advance,
Glenn Thimmes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 05:49:53

I think this have to with KERBEROS user authentication.
You can write a function that does the convert for you.
Try this

CREATE FUNCTION dbo.fnConvertUserName
(
)
RETURNS SYSNAME
AS
BEGIN
	RETURN	CASE
			WHEN SUSER_SNAME() LIKE '%@%.%' THEN REPLACE(PARSENAME(SUSER_SNAME(), 2), '@', '\')
			WHEN SUSER_SNAME() LIKE '%\%' THEN SUSER_SNAME()
			ELSE NULL
		END
END
GO

select dbo.fnConvertUserName()

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources