How to get login name and hostname

[cyno]

Hi

We have a table in which we need to audit the deletes. And iam creating a trigger for delete.I know we can use the user_name to get the DB user. Is there a way to get the workstation(hostname)name and login information as we have standard logins on that server and its hard to find which user has run the delete command.

Cyno

 

[DrSql]

Here is a script that I use.. You can try to minupulate


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpspwho2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmpspwho2]
GO

CREATE TABLE [dbo].[tmpspwho2] (
[SPID] [int] NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BlkBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DbName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Command] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUTime] [int] NULL ,
[DiskIO] [int] NULL ,
[LastBatch] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPID1] [int] NULL
) ON [PRIMARY]
GO
insert into tmpspwho2 exec('sp_who2')
GO

select login,HostName from tmpspwho2
where login=SUSER_SNAME ()
group by login,HostName
go
drop table tmpspwho2ate it


Dr.Sql
Good Luck.

 

[mrdenny]

You can also pull it directly from the sysprocesses table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[cyno]

Thank you so much.