Home » SQL ServerRSS

use EVENTDATA() to get hostname?

 

Hello.

 

We have an 3rd party app that I would like to track some schema changes in on 2005 SP2.

 

The app has it's own authentication layer that is not windows or sql logins, well almost.

 

However when the app drops down to the database level it uses the same sql login for all users, which is, wait for it, a member of the db_owner role. 

 

Now a number of developers we have make schema changes via the app and they all play dumb when another starts asking who changed this field or that field etc.  The app doesn't record the changes either and I can't tell because the login name is always the same.

 

I have a DDL trigger (code below, sorry I cant help the formatting?? it looks ok in SSMS)  that I use to track change in other db's that use either windows or sql logins which does the job but doesn't give me what I would ideally like in this particular case.

 

My Question is can EVENTDATA() return the hostname that sent the request? I mean hostname as in what is returned by sp_who

 

Because if I knew this I could track the change back to users PC and I could shut all the developers up :-) which would be nice.

 

Thanks.

 

 

CREATE

 

TRIGGER [TR_SchemaChangeLog]

ONDATABASE

FOR

 

CREATE_FUNCTION

, ALTER_FUNCTION, DROP_FUNCTION,

CREATE_INDEX

, ALTER_INDEX , DROP_INDEX,

CREATE_ROLE

, ALTER_ROLE , DROP_ROLE,

CREATE_TABLE

, ALTER_TABLE , DROP_TABLE,

CREATE_TRIGGER

, ALTER_TRIGGER , DROP_TRIGGER,

CREATE_USER

, ALTER_USER , DROP_USER,

CREATE_VIEW

, ALTER_VIEW , DROP_VIEW

AS

SET

 

NOCOUNTON

DECLARE

 

@data XML

SET

 

@data =EVENTDATA()

INSERT

 

INTO DBA_Admin.dbo.SchemaChangelog

(

 

databasename,

eventtype

,

objectname

,

objecttype

,

sqlcommand

,

loginname

)

VALUES

 

(

@data

.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(256)'),

@data

.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),

@data

.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(256)'),

@data

.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(25)'),

@data

.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'),

@data

.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(256)')

)

GO

 

7 Answers Found

 

Answer 1

The short answer is no, since the element "hostname" is not part of the schema  for the "alter table" event.

Here is the schema for the "alter table" event (from BOL):

<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
  <LoginName>name</LoginName>
  <UserName>name</UserName>
  <DatabaseName>name</DatabaseName>
  <SchemaName>name</SchemaName>
  <ObjectName>name</ObjectName>
  <ObjectType>type</ObjectType>
  <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

May be you can use the text value of the "SPID" element, which I think is the session_id, and lookup into sys.dm_exec_sessions to find the info you are looking for.

 

AMB

 

Answer 2

cheers, I did cross my mind that if it can get the SPID it should be able to get the hostname  by itself too.

I will give it a go at joining it up to the sys. tables

 

 

Answer 3

bit dirty but this works for CREATE TABLE, haven't tested the other events yet but I'll see how it goes. I had to alter my physical table and the trigger. Here it is for anyone else. Formatting on this forum drives me mad. 

CREATE

 

TABLE [dbo].[SchemaChangeLog](

[LogId] [int]

IDENTITY(1,1)NOTNULL,

[DatabaseName] [varchar](256)NOTNULL,

[EventType] [varchar]

(50)NOTNULL,

[ObjectName] [varchar]

(256)NOTNULL,

[ObjectType] [varchar]

(25)NOTNULL,

[SqlCommand] [varchar]

(max)NOTNULL,

[EventDate] [datetime]

NOTNULL,

[LoginName] [varchar]

(256)NOTNULL,

[SPID] [int]

NOTNULL,

[hostname] [varchar]

(256)NOTNULL

)

 

GO

CREATE

 

TRIGGER [TR_SchemaChangeLogTest]

ON

 

DATABASE

FOR

 

CREATE_TABLE

AS

SET

 

NOCOUNTON

DECLARE

 

@data XML

SET

 

@data =EVENTDATA()

CREATE

 

TABLE #temp (

[DatabaseName] [varchar]

(256)NOTNULL,

[EventType] [varchar]

(50)NOTNULL,

[ObjectName] [varchar]

(256)NOTNULL,

[ObjectType] [varchar]

(25)NOTNULL,

[SqlCommand] [varchar]

(max)NOTNULL,

[LoginName] [varchar]

(256)NOTNULL,

[SPID] [int]

NOTNULL,

[hostname] [varchar]

(256)NULL

)

 

INSERT

 

INTO #temp

(

databasename

,

eventtype

,

objectname

,

objecttype

,

sqlcommand

,

loginname

,

HostName

,

SPID

)

VALUES

 

(

@data

.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(256)'),

@data

.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),

@data

.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(256)'),

@data

.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(25)'),

@data

.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'),

@data

.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(256)'),

 

NULL,

@data

.value('(/EVENT_INSTANCE/SPID)[1]','varchar(256)')

 

)

INSERT

 

INTO dbo.SchemaChangeLog

SELECT

 

a.databasename,

a

.eventtype,

a

.objectname,

a

.objecttype,

a

.sqlcommand,

 

GETDATE(),

a

.loginname,

a

.SPID,

b

.hostname

FROM

 

#temp a

JOIN

 

sys.sysprocesses b

ON

 

a.spid = b.spid

 

 

 

 

Answer 4

> Formatting on this forum drives me mad.

When replying or posting, there is a menu item for posting code block "</> - Insert Code Block". There you can select the language you want to format the block based on, like T-SQL.

 

AMB

 

Answer 5

Why not use the host_name() function?

 

Answer 6

Why not use the host_name() function?


Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Thank you Erland, I am just not familiar with all the functions given that they are so many!

I have updated my T-SQL accordingly,  to create the table you need to hold the changes:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SchemaChangeLog](
	[LogId] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [varchar](256) NOT NULL,
	[EventType] [varchar](50) NOT NULL,
	[ObjectName] [varchar](256) NOT NULL,
	[ObjectType] [varchar](25) NOT NULL,
	[SqlCommand] [varchar](max) NOT NULL,
	[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
	[LoginName] [varchar](256) NOT NULL,
	[Workstation] [varchar](256) NOT NULL
) ON [PRIMARY]

GO

next the trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATETRIGGER [TR_SchemaChangeLog]
ONDATABASEFOR DDL_DATABASE_LEVEL_EVENTS 
ASSETNOCOUNTONDECLARE @data XMLSET @data = EVENTDATA()

INSERTINTO DBA_Admin.dbo.SchemaChangelog
(
	databasename,
	eventtype, 
	objectname,	
	objecttype, 
	sqlcommand, 
	loginname, 
	Workstation
)
VALUES(
	@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
	@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
	@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
	@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
	@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
	@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'), 
	HOST_NAME()
)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [TR_SchemaChangeLog] ONDATABASE

now this is all well and good if you create the SchemaChangeLog table in the same database as the trigger as you won't have any permissions problems.

But if you want to collect the changes for all db's in a "central" database then you'll have to sort the permissions out.

The easiest (but not necessarily the most secure) way to achieve this is:-

create a db called DBA_Admin (or something similar) In this database, create the SchemaChangeLog from the T-SQL above re-enable the guest account in the DBA_Admin db grant the public role INSERT permissions on the SchemaChangeLog table create the trigger in database(s) that you want to monitor 

Now because I have re-enabled the guest account (which is disabled by default) and granted the INSERT permissions for the SchemaChangeLog to the public role it allows any login (SQL or Windows) to be able to insert into the SchemaChangeLog table without having to explicilty grant them permission within the DBA_Admin database.

Now I know that altering the default behaviour of the guest account and public roles isn't probably the recommended thing to do it does save you having to manage user accounts in the DBA_Admin db. See public in BOL for why this works.

As I am only doing this in a dev environment this works for me.

If anyone has a more secure way of doing this I'm all ears but one thing I really dont want to do in this thread is to open yet another debate on the guest account and the public role, there are enough already!

Cheers

PS -  thanks to Hunchback for the formatting tip

 

 

 

 

Answer 7

> Thank you Erland, I am just not familiar with all the functions given that they are so many!

In Books Online, there is a node Functions in the T-SQL Reference, and under this node you find function categorized, and System functions includes a few more interesting functions.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter