Creating Trigger we can get the details of Logon user.
I will not discuss more on this topic because following example explain itself how It is working on the server. Just Run the following Script and sea the Result.
-- We are using AdventureWorks Database
-- Create Audit Table For Insert the History Of Logon User
CREATE TABLE SQLServerLogonHistory
(SystemUser VARCHAR(512),DBUser VARCHAR(512),SPID INT,LogonTime DATETIME)
-- Now here We are creating the Logon Trigger.
CREATE TRIGGER Tr_SQLServerLogonHistory
ON ALL SERVER FOR LOGON
INSERT INTO AdventureWorks.dbo.SQLServerLogonHistory
Note:- Please Try It. Very Important for Audit Purpose.
-- After creating Trigger see the Table Object.
Select * From AdventureWorks.dbo.SQLServerLogonHistory
Note : After Testing this example Please Do this two things-
Go to the RUN -> CMD
Write :- C:/>sqlcmd –S LocalHost –d master –A
-- In SQL Server Run this script for drop the Trigger
Drop TRIGGER Tr_SQLServerLogonHistory ON ALL SERVER
Question: But I have one doubt Why the table increasing continuously with same records where as I am not login multiple time?
If any body have Answer of this question then please Reply.