User ID without Domain name

Our DBAs made some change to the servers so now when we call USER it returns domain\username instead of just username.

Is there anyway I can get the system to just return me the username?

I've tried these...
select user_id(user)
select suser_sname(suser_sid(user))
select current_user

I've been using this code to get the userid since the change
--------------------------------------------------------------------------------
-- Get userid
declare @backslash_position int, @user varchar(10)
set @backslash_position = charindex('\', user, 1)
if @backslash_position > 0
begin
      set @user = substring (user, @backslash_position + 1, 10)
end
else
begin
      set @user = user
end      

If there is no option to get just the username back can I rely on the server to always send in the domain\username format?

Thanks,
Bruce
LVL 1
BruceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

myrthnCommented:
What version of SQL Server are you running?  What kind of authentications are permitted - 'Windows only' or 'Windows and SQL Server'?

~myrthn
pcsentinelCommented:
If your DBA has changed things to Windows Only authentication then the username will always be prefixed with the domainname.

If the DBA has set Windows and SQL Server authentication then it entirely depends on how the users connect to the database.

i.e. if you open query analyzer and connect using windows authentication then

SELECT USER will show DOMAIN/USER

but if you connect with SQL Server authentication then

SELECT USER will show USER

regards

Scott PletcherSenior DBACommented:
>> Is there anyway I can get the system to just return me the username? <<

That is just the username.  As background, remember that in SQL Server, the username is the name within the user db; the login name is the name in the master db.

The login name and the username do *not* necessarily have to be the same.  By default, SQL used to drop the domain name from the login name when determing the username.  Now, by default, it leaves the domain part on the username.  [This can be overridden by the dbas if desired.  In fact, you could use a username of "yyy" for a login name of "myDomain\myUser" if you wanted to; they are *separate* values, although they are linked.]

So, the username may or may not have a domain part in it, depending on how the dba / db_owner wants to set it up.


You can, however, shorten your code considerably:

SET @user = SUBSTRING(user, CHARINDEX('\', user) + 1, 500)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BruceAuthor Commented:
Thanks to all who responded!

We use Windows authentification so I guess I am stuck.  Since I have little if any cooperation from the DBAs I will just have go with assuming the domain\user format for my trigger.

Thanks again,
Bruce
Scott PletcherSenior DBACommented:
Please note that this code:

SET @user = SUBSTRING(user, CHARINDEX('\', user) + 1, 500)

should work *whether or not* a '\' is present; for example:


DECLARE @username VARCHAR(50)

SET @username = 'domainName\userName'
SELECT SUBSTRING(@username, CHARINDEX('\', @username) + 1, 500)

SET @username = 'userNameOnly'
SELECT SUBSTRING(@username, CHARINDEX('\', @username) + 1, 500)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.