December 19, 2011 at 8:32 am
Hi
I want to Monitor My SQL Server's Database events. Whenever there would be a change in My Schema,Execution of SP/Trigger/View/DDL/DML part>> The User's Machine Name emailed to my INBOX>>>
PS: It doesn't Matter whatever the Machine is Placed<< Within LAN or out of my LAN But ONE THING IS CONFORMED>> The ALL USERS are ONLY My LAN Users.. I only want them their Machine Name. I have the Mechanism to translate them into User Name of my Domain.
Same Login is being used by Multiple Users. Not Login Name BUT ONLY Machine Name of User Must By Traceable.
PS: I dont want the server name on which SQL Installaed. Only User's Machine Name.
Looking forward of your answer.
Thanks
WSBukhari
December 19, 2011 at 9:28 am
You can use a DDL trigger to capture the event. Here's a sample:
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
The eventdata doesn't have the machine name, but it will have the login name
December 20, 2011 at 8:19 am
Thanks Boy:)
I have tested this and found this area un-useful for me.Because i need only Machine Name as per my goal.
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
PS: How would i embedd thos into that trigger. Can you do this for me if it works?
SELECT hostname
FROM master.dbo.sysprocesses
WHERE 50 <= spid
December 20, 2011 at 9:24 am
What are you trying to embed in the trigger? The query from sysprocesses?
Use the value from EventData to find the machine name. I'm not sure why you are querying less than 50 for the SPID. What you ought to do is something more like.
select hostname
from master.sys.dm_exec_sessions
where login_name = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'))
and assign that to a variable you insert into your logging table.
December 20, 2011 at 9:25 am
Sorry, the DM view: http://msdn.microsoft.com/en-us/library/ms176013.aspx
Don't use sysprocesses. It's old
December 20, 2011 at 10:55 pm
waseem.shahzad 45937 (12/19/2011)
HiI want to Monitor My SQL Server's Database events. Whenever there would be a change in My Schema,Execution of SP/Trigger/View/DDL/DML part>> The User's Machine Name emailed to my INBOX>>>
@steve-2 : Will DDL capture execution of view/sp /trigger also ? i am not aware of this
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 20, 2011 at 11:08 pm
Bhuvnesh (12/20/2011)
waseem.shahzad 45937 (12/19/2011)
HiI want to Monitor My SQL Server's Database events. Whenever there would be a change in My Schema,Execution of SP/Trigger/View/DDL/DML part>> The User's Machine Name emailed to my INBOX>>>
@steve-2 : Will DDL capture execution of view/sp /trigger also ? i am not aware of this
Yes, it will.
Per BOL:
DDL triggers, like standard triggers, execute stored procedures in response to an event. But unlike standard triggers, they do not execute in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they primarily execute in response to data definition language (DDL) statements. These include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.
For More: http://msdn.microsoft.com/en-us/library/ms175941.aspx
December 21, 2011 at 12:14 am
Bhuvnesh (12/20/2011)
waseem.shahzad 45937 (12/19/2011)
HiI want to Monitor My SQL Server's Database events. Whenever there would be a change in My Schema,Execution of SP/Trigger/View/DDL/DML part>> The User's Machine Name emailed to my INBOX>>>
@steve-2 : Will DDL capture execution of view/sp /trigger also ? i am not aware of this
No. It capture only schema change. Not execution.
December 21, 2011 at 12:27 am
Suresh B. (12/21/2011)
Bhuvnesh (12/20/2011)
waseem.shahzad 45937 (12/19/2011)
HiI want to Monitor My SQL Server's Database events. Whenever there would be a change in My Schema,Execution of SP/Trigger/View/DDL/DML part>> The User's Machine Name emailed to my INBOX>>>
@steve-2 : Will DDL capture execution of view/sp /trigger also ? i am not aware of this
No. It capture only schema change. Not execution.
Thanks for correction Suresh. I overlooked the 'execution' word. I read it as 'DDL of view/sp /trigger'.
December 21, 2011 at 3:33 am
Thanks for your Acknowlegement.
Whats Final <Script>
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply