user_name() vs system_user - why does one not return the correct name?

Question

user_name() vs system_user - why does one not return the correct name?

Pete T-366679

SSCrazy

Points: 2065
More actions
May 2, 2007 at 7:52 am

#103354

We have an inhouse application that uses stored procedures on SQL Server 2000 and 2005. In the stored procs, "user_name()" is used extensively to get the currently logged in user, so we can track who does what.
It's recently come to our attention that for some users (admins) user_name() is returning "dbo" instead of the logged in username. These are users that are members of various server admin roles, but they use the application, too.
What is the correct way to return the current logged in user? It appears "system_user" is correct, but why doesn't user_name() work? Or current_user for that matter? Is this a setting somewhere? As far as I know nothing has changed on our setup in this regard, but it's strange that we just started noticing it. Any help or insight would be awesome. Thanks!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think what you are seening is whenever anyone is given the role db_owner(), their user_name is considered to be dbo for the queries performed.... since everyone else comes in as db_datareader and db_datawriter rights, they are not substituted out as dbo does.

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!

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 2

Thanks for the reply. So if we have certain admin users who log into SQL server but who we want to be able to perform dbo roles, what is the best practice to do that? Or is "system_user" the preferred method to definitely get the logged in username?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

The best practice is for those users to have 2 logins. One admin/dbo and the other just a plain user. They should use the admin/dbo login only when needed, otherwise use the less privledged login for all other activities. Barring that, you should prbably use system_user to identify the user.

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 4

Thanks. Basically, we have designers/developers with SQL logins, but they also do things within the application (which records the username). It seemed better to have one login, and just give whatever permissions are needed to that login.

I'll look into replacing user_name() with system_user in all the stored procedures. Thanks again!

Terri-92562 SSCommitted Points: 1614 More actions · Answer 5

Use suser_sname(). This should return the correct name. I believe that the name returned by user_name() changed between SQL 6.5 and SQL7. BOL tells you that user_name() returns dbo for someone in role sysadmin.

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]

Rob Maurer SSC Veteran Points: 242 More actions · Answer 6

I use this to accomplish a similar goal:

ALTER FUNCTION dbo.CurrentUser ()

RETURNS VARCHAR (50)

BEGIN

DECLARE @sys_usr char(80), @name char(50),

@pos int, @length int

SET @sys_usr = SYSTEM_USER

set @length = len(@sys_usr)

set @pos = charindex('\', @sys_usr)

set @name = substring(@sys_usr, @pos + 1, @length)

return @name

END