September 26, 2007 at 8:30 am
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.
September 26, 2007 at 8:50 am
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
September 26, 2007 at 8:52 am
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
September 26, 2007 at 10:04 am
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.
September 26, 2007 at 10:13 am
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.
September 26, 2007 at 10:20 am
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.
September 26, 2007 at 10:25 am
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.
September 26, 2007 at 11:53 am
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