Post reply

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

  • 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!

  • May 2, 2007 at 8:40 am

    #703825

    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!

  • May 2, 2007 at 8:44 am

    #703830

    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?

  • May 2, 2007 at 9:16 am

    #703850

    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.

  • May 2, 2007 at 9:31 am

    #703857

    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!

  • May 3, 2007 at 6:24 am

    #704041

    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.]

  • May 3, 2007 at 6:31 am

    #704042

    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

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

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