Track Connected Client Information

Question

Track Connected Client Information

skrilla99

SSCertifiable

Points: 5795
More actions
February 10, 2004 at 2:54 pm

#82158

Hi all,
I am interested in tracking certain client information for all users who connect to my sql servers. Specifically, I would like to track the specific program they are using to connect and, if via sql server, which version and service pack level. Sysprocesses was my first thought, but not detailed enough!
Any thoughts?
Thanks!
- Dan B

Viewing 5 posts - 1 through 4 (of 4 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Here is the script I used to trace the user access to my system. I schedued it to run every one minutes. It may not complete statisfy your requirement.

declare @host varchar(50)

declare @login varchar(50)

declare @dbname varchar(50)

declare @message varchar(1000)

declare @program_name varchar(500)

declare whois cursor

for select distinct

rtrim(hostname) as hostname,

rtrim(loginame) as loginame,

rtrim(db_name(dbid)) as dbname,

rtrim(program_name) as program_name

from master.dbo.sysprocesses

where len(hostname) > 0

and program_name = 'microsoft® access'

open whois

fetch next from whois into @host, @login, @dbname, @program_name

while @@fetch_status = 0

begin

select @message = 'xp_cmdshell ' + char(39) + 'net send xxxxxxxxx User ' + @host + ' is accessing ' + @dbname + ' data with ' + @program_name + '.' + char(39)

execute (@message)

print (@message)

fetch next from whois into @host, @login, @dbname, @program_name

end

deallocate whois

If you need completed information, run Profiler all the time to your system.

skrilla99 SSCertifiable Points: 5795 More actions · Answer 2

Thanks Allen.

That looks like a handy script. Unfortunately it does not provide all the information I needed. I tried all the options and columns in profiler that looked close, but no luck. It may be the case that that data regarding specific client application and service pack level is not sent to the server, in which case what I want is impossible.

Thanks again.

-Dan B

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

The client has to input their application name is the DSN connection string. Otherwise, It wouldn't show up in sysprocesses.

skrilla99 SSCertifiable Points: 5795 More actions · Answer 4

But, what about clients connecting directly via EM? Is it possible to collect their local sql service pack level?

-Dan B