Machine ID
-
Can I get the name/id of the machine in a trigger that I can use for populating as a user id in a table?
eg. I fmy machine name is p833-venus how can I get this in the trigger?
P.S Do you think that getting the machine is a good idea or should I get the ip addr to be really unique. If so how can I get it within a trigger?
Thanks
-
I think you'll need to pass this from the user interface.
-
you can get the hostname() from the system, but this is passed by the client. Also, because of proxy servers and NAT, IP addresses are not necessarily unique.
Steve Jones
-
Steve/Deuce
So what do you think is the best way of getting an unique number/id/name so that I can identify that that box was used for this trigger to fire. Thats what I am looking for..
Thanks guys for your help!!
-
Steve,
How do you get Hostname from the system? I got:
Server: Msg 195, Level 15, State 10, Line 1
'hostname' is not a recognized function name.
Can you identify the box by the User?
-
Sorry,
select host_name()
The user will be
select suser_name(), user_name()
depending on if you want login or db user. You can get all of these from sp_who
Steve Jones
-
Host_Name()
That's cool! I didn't know you could do that.
-
-
Steve,
I am going to go with the host_name(). That sounds like what I wanted..
Thanks
-
I'll get right on that.
-
Usually you'll want to capture user and host name. Host name is frequently wrong, a good example is Access if you set up a linked table on one machine then give someone a copy, usually has the wrong workstation name on the copy. A good case for NT authentication on user name, since with SQL logins you can't tell who it was, just how they accessed!
Andy
-
Andy is right. This is supplied through the ADO connection and there is no validation or verification. I can easily send a query from my workstation as host_name() "AWarren" if I want.
Steve Jones
-
Steve/Andy
If I understand it right the unique name will be user_name + host_name right? Now I dont understand your reasoning behind the combination to be unique. Could you please explain?
What I am trying to do is in my db there are certain tables which does not ahve the user_id field. Therefore to get the info into my transaction table from where the data got updated I thought I can use the host_name so I can pinpoint saying that machine was used to update. But I guess what you are saying is "that machine and that user" because a diff user could have used that machine? Are we on the same page or am I totally off?
Thanks
-
user_name will be unique IF your users do not share passwords or machines. host_name has nothing to do with it. I can hard code "awarren" in the host_name parameter for ADO for every workstation. If I hard coded a user, then there would be nothing unique. If you have every user with a unqiue password, user_name will work.
Steve Jones
-
I think the point Andy was making is that SQL will only give you the host_name() provided in the connection string. MS Access will store the machine name when you link the SQL table. No matter who uses that mdb, it will appear to SQL as the machine the link was created on. This was driving one of our developers nuts one day. sp_who make it looked like he had a TON of connections open from his machine. It turned out to be an mdb he distributed.
Andy, Steve,
I have a problem with user_name() or user. They return 'dbo' for the database owner. suser_name is null. Is there an easy way around this? (this question lead to the query below)
sujosh, would this work for you?
select
'user name: ' + rtrim(loginame) +
' on machine: '+ rtrim(hostname) +
' at ' + rtrim(net_address) +
' using ' + rtrim(program_name)
from
master..sysprocesses
where
spid = @@spid
, check out the system functions in BOL. Lots of goodies.Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply