October 28, 2011 at 6:09 am
Hi Guys!
See the trigger:-
create trigger backup_objects
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as
set nocount on
declare @data xml
set @data = EVENTDATA()
insert into eventslogdb.dbo.changelog(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
Its working fine for me.I get it from here":
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
How would i get the machine name as well in thsi trigger?
Thanks in advance.
October 28, 2011 at 6:24 am
This was removed by the editor as SPAM
October 28, 2011 at 6:30 am
Let me clear you the scenario:-
One Login
Mutilpe Users.
Off course in this case only single Username would be output. But the purpose is to track the related client machines.
I hope you have understand my Query.
Thanks in advance.
October 28, 2011 at 6:40 am
Add in
@data.value('(/EVENT_INSTANCE/HostName)[1]', 'varchar(256)')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2011 at 6:44 am
This was removed by the editor as SPAM
October 28, 2011 at 8:04 am
See the error.
Msg 102, Level 15, State 1, Procedure backup_objects, Line 23
Incorrect syntax near '@data'.
October 28, 2011 at 8:07 am
Here is the script i edited fo this.
Create trigger backup_objects
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as
set nocount on
declare @data xml
set @data = EVENTDATA()
insert into eventslogdb.dbo.changelog(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)')
@data.value('(/EVENT_INSTANCE/HostName)[1]', 'varchar(256)')
)
go
October 28, 2011 at 8:13 am
This was removed by the editor as SPAM
October 28, 2011 at 9:50 am
But this Hostname is throwing only the server Name. Not the Machine name of User.
STRANGE :S
October 28, 2011 at 10:02 am
Then the SQL connection is being initiated from the server. The hostname is the name of the machine that requests the connection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 12:29 am
Solution Pls?
October 31, 2011 at 2:05 am
If the hostname is showing the server name, then the SQL connection is being initiated from the server and, as far as SQL is concerned, where the connection starts is the host.
Now, you know the system, I don't. Is the SQL connection starting from the server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply