Obtaining Window login name from a trigger

Hi,
We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know ho
w I accomphish this task.
Try SUSER_SNAME function:
http://msdn.microsoft.com/library/de...u-sus_86xx.asp
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
"Herb" wrote:

> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know
how I accomphish this task.
|||Hi,
Thanks for your suggestion. When I issue SUSER_SNAME while using Window Authication, I do get the Window user ID. However, when I issue SUSER_SNAME while using SQL login, I get the SQL login(for ex. SQL login = RUSER, SUSER_SNAME retrieves RUSER).
"Narayana Vyas Kondreddi" wrote:

> Try SUSER_SNAME function:
> http://msdn.microsoft.com/library/de...u-sus_86xx.asp
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||You'll need to pass the "real" login name through to SQL. One technique for
this when an application uses a single service account is to use SET
CONTEXT_INFO to pass the information through with the user connection and
pull this out in your trigger (i. Note that you have to explicitly set this
in your app for each connection that you require this information for. In
some configurations this may not be practical/possible. See BOL for more on
the command.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||Thanks for the suggestion. I will give that a try.
"Jasper Smith" wrote:

> You'll need to pass the "real" login name through to SQL. One technique for
> this when an application uses a single service account is to use SET
> CONTEXT_INFO to pass the information through with the user connection and
> pull this out in your trigger (i. Note that you have to explicitly set this
> in your app for each connection that you require this information for. In
> some configurations this may not be practical/possible. See BOL for more on
> the command.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||Thanks for that information. We still have some apps that are using COM+
Herb
"Shawn Brock" wrote:
[vbcol=seagreen]
> If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
> "Herb" wrote:
w how I accomphish this task.

Obtaining Window login name from a trigger

Hi,
We are running SQL 2K with SQL Server and Windows authentication. Our syste
m has been designed to login external user to SQL Server using one login acc
ount. I am trying to retrieve that actual Windows login user name from a tr
igger. Does anyone know ho
w I accomphish this task.Try SUSER_SNAME function:
http://msdn.microsoft.com/library/d.../>
us_86xx.asp
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.|||If you are using something like MTS / COM+ then you're not going to be able
to get that information unless the component explicitly passes in the user n
ame.
"Herb" wrote:

> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our system has
been designed to login external user to SQL Server using one login account. I am tr
ying to retrieve that actual Windows login user name from a trigger. Does anyone kn
ow
how I accomphish this task.|||Hi,
Thanks for your suggestion. When I issue SUSER_SNAME while using Window Aut
hication, I do get the Window user ID. However, when I issue SUSER_SNAME wh
ile using SQL login, I get the SQL login(for ex. SQL login = RUSER, SUSER_S
NAME retrieves RUSER).
"Narayana Vyas Kondreddi" wrote:

> Try SUSER_SNAME function:
> http://msdn.microsoft.com/library/d...
-sus_86xx.asp
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user nam
e
> from a trigger. Does anyone know how I accomphish this task.
>
>|||You'll need to pass the "real" login name through to SQL. One technique for
this when an application uses a single service account is to use SET
CONTEXT_INFO to pass the information through with the user connection and
pull this out in your trigger (i. Note that you have to explicitly set this
in your app for each connection that you require this information for. In
some configurations this may not be practical/possible. See BOL for more on
the command.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.|||Thanks for the suggestion. I will give that a try.
"Jasper Smith" wrote:

> You'll need to pass the "real" login name through to SQL. One technique fo
r
> this when an application uses a single service account is to use SET
> CONTEXT_INFO to pass the information through with the user connection and
> pull this out in your trigger (i. Note that you have to explicitly set thi
s
> in your app for each connection that you require this information for. In
> some configurations this may not be practical/possible. See BOL for more o
n
> the command.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user nam
e
> from a trigger. Does anyone know how I accomphish this task.
>
>|||Thanks for that information. We still have some apps that are using COM+
Herb
"Shawn Brock" wrote:
[vbcol=seagreen]
> If you are using something like MTS / COM+ then you're not going to be abl
e to get that information unless the component explicitly passes in the user
name.
> "Herb" wrote:
>
w how I accomphish this task.

Obtaining Audit Information

Hi People

We want one trigger which captures the data as mentioned below.

Table Name

Date and Time

User

type

Mode of modification

TABLE_ABC

03/07/2007 12:00:04

XCS\Raoa

Update

Procedure

TABLE_DEF

03/07/2007 12:00:34

XCS\Raoa

Insert

Class Integration SSIS Package

TABLE_GHI

03/07/2007 12:01:04

XCS\Raoa

Insert

Procedure

TABLE_GHI

03/07/2007 12:01:34

XCS\Raoa

Update

XCS\Raoa (Manual)

I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.

Could someone help me achieving this?

Regards

Abhi

Hi Abhi,

I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:

Code Snippet

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[T_DDL_DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[ObjectName] [sysname] NOT NULL,

[CommandText] [varchar](max) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [DDL_Alter_Audit]

ON DATABASE

FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER

AS

DECLARE @.eventData XML

SET @.eventData = eventdata()

INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)

SELECT

GETDATE() AS EventDate,

@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

AS UserName,

@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

AS ObjectName,

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE

Also, you can find more informationabout DDL triggers in the BOL.

I hope it helps.

Regards,

Janos

|||

Thanks for replying Janos.

What does following statement return?

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

Anyways, I m looking for DML triggers. Whenever someone inserts/updates the table, I wanna know how that table was manipulated..was it manipulated using SSIS package or stored procedure or manually by some user. Is it possible to capture this information

|||

Hi,

CommandText returns the T-SQL script ran against the object. Eg.: In got a table called Table1. when I'm going to alter it, the Command text will contain the altering sql script, like ALTER TABLE Table1 .....

LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.

You can make this DDL trigger for all database events with DDL_DATABASE_LEVEL_EVENTS event, but this still monitors the objects, not monitoring your DML actions. You should write DML triggers for all your tables required audit.

Regards,

Janos

|||

Thanks for you reply Janos.. Cheers!!!

Obtaining Audit Information

Hi People

We want one trigger which captures the data as mentioned below.

Table Name

Date and Time

User

type

Mode of modification

TABLE_ABC

03/07/2007 12:00:04

XCS\Raoa

Update

Procedure

TABLE_DEF

03/07/2007 12:00:34

XCS\Raoa

Insert

Class Integration SSIS Package

TABLE_GHI

03/07/2007 12:01:04

XCS\Raoa

Insert

Procedure

TABLE_GHI

03/07/2007 12:01:34

XCS\Raoa

Update

XCS\Raoa (Manual)

I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.

Could someone help me achieving this?

Regards

Abhi

Hi Abhi,

I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:

Code Snippet

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[T_DDL_DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[ObjectName] [sysname] NOT NULL,

[CommandText] [varchar](max) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [DDL_Alter_Audit]

ON DATABASE

FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER

AS

DECLARE @.eventData XML

SET @.eventData = eventdata()

INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)

SELECT

GETDATE() AS EventDate,

@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

AS UserName,

@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

AS ObjectName,

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE

Also, you can find more informationabout DDL triggers in the BOL.

I hope it helps.

Regards,

Janos

|||

Thanks for replying Janos.

What does following statement return?

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

Anyways, I m looking for DML triggers. Whenever someone inserts/updates the table, I wanna know how that table was manipulated..was it manipulated using SSIS package or stored procedure or manually by some user. Is it possible to capture this information

|||

Hi,

CommandText returns the T-SQL script ran against the object. Eg.: In got a table called Table1. when I'm going to alter it, the Command text will contain the altering sql script, like ALTER TABLE Table1 .....

LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.

You can make this DDL trigger for all database events with DDL_DATABASE_LEVEL_EVENTS event, but this still monitors the objects, not monitoring your DML actions. You should write DML triggers for all your tables required audit.

Regards,

Janos

|||

Thanks for you reply Janos.. Cheers!!!