July 22, 2011 at 2:36 am
Hello All,
Please help me in tracking the user activities on my DB.
1. who logged into a DB
2. when logged
3. when was the last connection
I would like to have audit to be recorded into a table.
I tried using DDL Triggers, but failing to connect the instance after.
Please help me in identifying this.
Thanks in advance.
- Win.
Cheers,
- Win.
" Have a great day "
July 22, 2011 at 4:26 am
I suspect this is not the right forum: logon triggers are not available in SQL Server 7 or 2000.
However, what is failing in your logon trigger?
-- Gianluca Sartori
July 22, 2011 at 4:30 am
Sorry, to write here.. I will mark again this into SQL 2005.
Actually I was trying to audit database logins using DDL Triggers.
Logon failed for the user 'sa' due to trigger execution.
I would like to capture the database logins and connections made.
Can anyone please help me in resolving this.
Cheers,
- Win.
" Have a great day "
July 22, 2011 at 5:09 am
Probably the user that connects to the database is requested to write an object (auditing table) that is outside of his permissions, due to the trigger execution. You could wrap it inside a stored procedure:
CREATE TABLE LogonEvents (
LoginName sysname,
EventDate datetime
)
GO
CREATE PROCEDURE AuditLogon @login sysname
AS
BEGIN
SET NOCOUNT ON
INSERT INTO LogonEvents (LoginName, EventDate)
SELECT @login, GETDATE()
END
GO
GRANT EXECUTE ON AuditLogon TO public
GO
CREATE TRIGGER [TR_LOGON]
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET NOCOUNT ON
DECLARE @login sysname
SET @login = ORIGINAL_LOGIN()
BEGIN TRY
EXEC AuditLogon @login
END TRY
BEGIN CATCH
-- This goes to the SQL Server Log
PRINT ERROR_MESSAGE()
END CATCH
END
GO
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply