` Printed Icetips Article

Icetips Article

Par2: Who's logged on -- SQL
2009-03-12 -- J Shankar

>Does anyone know how I can read the MS SQL sp_hook to determine in a station 
>is logged on. This is my thinking, I would like to write a function that can 
>allow a user to logon only once to the database. 

I use a query shown below to find out all users logged into a database ::

SELECT master.dbo.sysprocesses.nt_username AS NetworkUserName, 
       master.dbo.sysprocesses.loginame AS LoginName, 
       convert(char(20),master.dbo.sysprocesses.login_time,113) AS LoginTime, 
       master.dbo.sysdatabases.name AS DatabaseName,
       master.dbo.sysprocesses.hostname AS ComputerName,
       master.dbo.sysprocesses.net_address AS MACAddress,
       master.dbo.sysprocesses.program_name AS ProgramName
FROM   master.dbo.sysprocesses, master.dbo.sysdatabases
WHERE  master.dbo.sysdatabases.dbid = master.dbo.sysprocesses.dbid 
  AND  master.dbo.sysdatabases.name = ''

You can use the returned MACAddress to verify the workstation.

-----

I left out the last part of the WHERE query as I thought it was unnecessary but 
here is it ::

  AND  master.dbo.sysprocesses.program_name LIKE '%'

And, for the above query to work, I set my connection string as ::

        GLO:ConnectionString = CLIP(GLO:Server) & ',' & CLIP(GLO:Database) & 
',' & CLIP(GLO:DbUser) & ',' & CLIP(GLO:DbPWord) & ';APP=_' & 
CLIP(GLO:User) & CHOOSE(GLO:CompName = '', '', ';WSID=' & 
CLIP(GLO:CompName))

where GLO:User is the application user name and GLO:DbUser is the database/proxy user.

Printed May 6, 2024, 4:45 pm
This article has been viewed/printed 35123 times.