Finding system_user

  • Greetings folks. I am trying to find where the value for system_user comes from. I use enterprize manager to create logins for my users. When the new login dialog box pops up I enter name: which is for my users our company ID, also used as the windows login ID. I choose Windows authentication, then enter the domain. Then I go to the database access tab and check the box for my database access. At that point the value I entered in the name: box on the general tab pops up in the user field. I then change the person's windows login to their name, so I can more easily recognize the users. My problem is that when I want to capture the persons name in an update trigger, say, I use system_user which returns the windows login. I looked in every system table for this value and I can not find it. In the sysusers table I did find their names in the name column but no trace of the windows login. Does anyone know how I can retrieve this value?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • you can use the function USER_ID() and USER_NAME() inside your trigger, and there is a bunch of system functions like APP_NAME and others that might be helpful for auditing/logging. look in books on line for "SYystem Functions" for the complete list

    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!

  • Greg Snidow (9/26/2007)


    Greetings folks. I am trying to find where the value for system_user comes from. I use enterprize manager to create logins for my users. When the new login dialog box pops up I enter name: which is for my users our company ID, also used as the windows login ID. I choose Windows authentication, then enter the domain. Then I go to the database access tab and check the box for my database access. At that point the value I entered in the name: box on the general tab pops up in the user field. I then change the person's windows login to their name, so I can more easily recognize the users. My problem is that when I want to capture the persons name in an update trigger, say, I use system_user which returns the windows login. I looked in every system table for this value and I can not find it. In the sysusers table I did find their names in the name column but no trace of the windows login. Does anyone know how I can retrieve this value?

    Greg,

    Try this table "master.dbo.sysxlogins". From my previous experience, system_user returns the "name" column from this table.

    User_Name() and Current_User would work too, however, if the user is sysadmin, the name returns become "dbo."

    Regards,

    Wameng Vang

    MCTS

  • Thanks Lowell for the quick reply. I figured out that the name I enter in the name column on the database access tab of the login dialog is what shows up in sysuers.name. I can't think of how best to explain my problem, but here goes. Lets say I have Bob who wants access to my database. Bobs company ID is B101 which is used as his windows login id, and the domain is US1 for my database. So, when I open the new login dialog box I enter 'B101' in the Name field, then choose 'US1' from the domain drop down. Then when I go to the database access tab and check my database the value I entered in the name field pops up, in this case 'B101'. What I do then is delete the value 'B101' and type 'Bob'. So far I think I know what is happening. I know that I can say SELECT user_id('Bob') and it will return the value in column sysusers.uid where name = 'Bob'. What I don't understand is that when I have a default value set to system_user in a table, what will be entered for Bob is not 'Bob', but a combination of domain and windows login, in this case 'US1\B101'. I can not find in any system table where this value is stored. If I expand the console root to security then look at the logins what I see are a list like 'US1\B101'. If I expand the console root to my database and look at users what I see is 'Bob'. Does any of this make sense?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • By jove thats it mengus! I have been looking for that forever it seems like. Now my question is how can I select mydb.syscolumns.name instead of master.sysxlogins.name in trigger or any other code for that matter? I do not see any id fields in the tables that link up

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Geez, I guess I did not see that huge sid column in both tables.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • That was so freakin easy. Thanks guys for your help. I never gave any thought to the master, nor did I realize I could retrieve stuff from other databases. The following gave me *exactly* what I wanted.

    SELECT

    NAME = u.name,

    LOGIN = x.name

    FROM sysusers u,

    master.dbo.sysxlogins x

    WHERE u.sid = x.sid

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Greg,

    Glad to be of assistance....

    Regards,

    Wameng Vang

    MCTS

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

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