June 18, 2015 at 7:50 am
mark.dickinson (6/18/2015)
Michael L John (6/17/2015)
Sorry, I saw this post and ran out the door after I published the code.......3. I don't think Database name is part of the EventData Function. Try this:
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
............
Thanks Michael. Tried, but ObjectName comes up NULL.
Appreciate the help though!
Ok, in the DDL auditing trigger I have in place, this line is working:
DECLARE @DatabaseName varchar(256) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
That being said, the events in this trigger are all DDL events. I do not think that it's a part of the logon event.
https://msdn.microsoft.com/en-us/library/hh213611.aspx
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 18, 2015 at 8:43 am
i think a logon trigger is the wrong device in this case.I don't think the database named in a connection string has much relevance.
i think the OP is really looking for "who touched my databases"
the login is a single event, which can return the default value of the database that occurs in the connection string; if create a new SSMS window, that's master for example.
when i change my database context or use a three part name for DatabaseName.Schema.Table, that's completely not tracked in a server logon trigger.
so that's potentially hundreds of commands i cna run, against all my database,s and the logon trigger only shows master.
i'd consider using extended events to audit queries instead. alternatively you might go witha server side trace,.
Lowell
June 18, 2015 at 8:44 am
Michael L John (6/18/2015)
mark.dickinson (6/18/2015)
Michael L John (6/17/2015)
Sorry, I saw this post and ran out the door after I published the code.......3. I don't think Database name is part of the EventData Function. Try this:
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
............
Thanks Michael. Tried, but ObjectName comes up NULL.
Appreciate the help though!
Ok, in the DDL auditing trigger I have in place, this line is working:
DECLARE @DatabaseName varchar(256) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
That being said, the events in this trigger are all DDL events. I do not think that it's a part of the logon event.
I tried it like this...
CREATE TRIGGER [Server_Logon_Trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
INSERT INTO master.dbo.Server_Logon_History(EventType, PostTime, LoginName, ClientHost, DatabaseName)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)'),
DECLARE @DatabaseName varchar(256) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
GO
...but it did not like the placement of the DECLARE and would not create the trigger. I moved the DECLARE line above the INSERT and put the DatabaseName line back as it was, which allows the trigger to create but changes nothing in the results.
I assume I am not placing the line into the correct location in the trigger create script.
June 18, 2015 at 8:49 am
Lowell (6/18/2015)
i think a logon trigger is the wrong device in this case.I don't think the database named in a connection string has much relevance.i think the OP is really looking for "who touched my databases"
the login is a single event, which can return the default value of the database that occurs in the connection string; if create a new SSMS window, that's master for example.
when i change my database context or use a three part name for DatabaseName.Schema.Table, that's completely not tracked in a server logon trigger.
so that's potentially hundreds of commands i cna run, against all my database,s and the logon trigger only shows master.
i'd consider using extended events to audit queries instead. alternatively you might go witha server side trace,.
Actually, it's just the opposite. I already know who can and cant touch the databases on this SQL instance. I am mostly concerned with 'if' a database is being touched, so, if out of 35 databases, I see 4 have not been logged into in over a month, I can look into deleting them.
June 18, 2015 at 9:01 am
mark.dickinson (6/18/2015)
Lowell (6/18/2015)
i think a logon trigger is the wrong device in this case.I don't think the database named in a connection string has much relevance.i think the OP is really looking for "who touched my databases"
the login is a single event, which can return the default value of the database that occurs in the connection string; if create a new SSMS window, that's master for example.
when i change my database context or use a three part name for DatabaseName.Schema.Table, that's completely not tracked in a server logon trigger.
so that's potentially hundreds of commands i cna run, against all my database,s and the logon trigger only shows master.
i'd consider using extended events to audit queries instead. alternatively you might go witha server side trace,.
Actually, it's just the opposite. I already know who can and cant touch the databases on this SQL instance. I am mostly concerned with 'if' a database is being touched, so, if out of 35 databases, I see 4 have not been logged into in over a month, I can look into deleting them.
ok, to a degree, you can get some of that data from the index stats, but the stats are only good from when the server was last restarted to the current time.
i use this as a procedure to get last accessed information;databases that are set to read only don't show up on this list, since their indexes don't update.
something like this as a scheduled job, which logs to a table intermittently, might be easier to implement.
--based on the ideas from
--http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx
;WITH AllDatabases
AS
(
SELECT database_id,name AS TheDatabase from master.sys.databases WHERE name NOT IN('master','tempdb','model','msdb')
),
myCTE
AS
(
SELECT
AllDatabases.TheDatabase,
statz.last_user_seek,
statz.last_user_scan,
statz.last_user_lookup,
statz.last_user_update
FROM AllDatabases
LEFT OUTER JOIN sys.dm_db_index_usage_stats statz
ON AllDatabases.database_id = statz.database_id
)
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
x.TheDatabase,
MAX(x.last_read) AS last_read,
MAX(x.last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x
GROUP BY TheDatabase
ORDER BY TheDatabase
Lowell
June 18, 2015 at 9:38 am
I may end up going that route, Lowell. Thanks.
As an aside, this is the Oracle code I'm essentially trying to emulate...
create or replace trigger tr_logon
begin
If user not in ('SYS','SYSTEM','SYSMAN') then
Update log_trail set TIME = sysdate where NAME = user;
If sql%rowcount = 0 then
Insert into log_trail values(user,sysdate,'LOGON');
End if;
Commit;
End if;
end tr_logon;
I end up with the most recent login to every schema/database in the instance. Makes it easy to know which are or are not actively used.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply