June 17, 2015 at 11:30 am
Hi,
Newbie to the forum.
I'm trying to find/write a logon trigger to insert the most recent login to a database, the name of the database, and the user who logged in, into a table I'd create in MASTER.
Ideally, I'd want only the most recent login for each database, to avoid exponential table growth in the table the trigger will populate.
Can anyone point me in the right direction? Thanks!
June 17, 2015 at 12:43 pm
You may use "Who Is Active" script.
http://sqlblog.com/files/folders/release/entry35240.aspx
Create an agent job (schedule for every 5 minutes or any frequency you need), use the code from the script to get user and database info you need, and write into an msdb table.
June 17, 2015 at 12:54 pm
seaport (6/17/2015)
You may use "Who Is Active" script.http://sqlblog.com/files/folders/release/entry35240.aspx
Create an agent job (schedule for every 5 minutes or any frequency you need), use the code from the script to get user and database info you need, and write into an msdb table.
I'll give it a look. Thanks.
June 17, 2015 at 1:01 pm
CREATE TRIGGER [Server_Logon_Trigger]
ON ALL SERVER
FOR LOGON
AS
INSERT INTO DBAUtilities.dbo.Server_Logon_History(EventType, PostTime, ServerName, LoginName, LoginType, SID, ClientHost, IsPooled)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit')
GO
Will this work?
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 17, 2015 at 1:31 pm
Michael L John (6/17/2015)
CREATE TRIGGER [Server_Logon_Trigger]
ON ALL SERVER
FOR LOGON
AS
INSERT INTO DBAUtilities.dbo.Server_Logon_History(EventType, PostTime, ServerName, LoginName, LoginType, SID, ClientHost, IsPooled)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit')
GO
Will this work?
Do my eyes deceive me? Is this the same Michael L John I met in Cleveland? Mr. "No Triggers" himself is suggesting a server-level trigger??? I'm going to have fun with this one. 😀
In all seriousness, this trigger doesn't do anything but track logins. There aren't any business rules and this is a good approach.
June 17, 2015 at 1:40 pm
Michael L John (6/17/2015)
CREATE TRIGGER [Server_Logon_Trigger]
ON ALL SERVER
FOR LOGON
AS
INSERT INTO DBAUtilities.dbo.Server_Logon_History(EventType, PostTime, ServerName, LoginName, LoginType, SID, ClientHost, IsPooled)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit')
GO
Will this work?
Okay, so, to use that, I'd need to first create a table called Server_Login_History, assuming in MASTER, to contain the data the trigger generates, right?
June 17, 2015 at 1:43 pm
mark.dickinson (6/17/2015)
Michael L John (6/17/2015)
CREATE TRIGGER [Server_Logon_Trigger]
ON ALL SERVER
FOR LOGON
AS
INSERT INTO DBAUtilities.dbo.Server_Logon_History(EventType, PostTime, ServerName, LoginName, LoginType, SID, ClientHost, IsPooled)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit')
GO
Will this work?
Okay, so, to use that, I'd need to first create a table called Server_Login_History, assuming in MASTER, to contain the data the trigger generates, right?
Michael's got the database name qualified in the insert statement. DBAUtilities is probably his repository of DBA-type stuff. You can create the table and store the data wherever you want, but if you have a DBA database, it makes sense.
June 17, 2015 at 1:46 pm
Ed Wagner (6/17/2015)
mark.dickinson (6/17/2015)
Michael L John (6/17/2015)
CREATE TRIGGER [Server_Logon_Trigger]
ON ALL SERVER
FOR LOGON
AS
INSERT INTO DBAUtilities.dbo.Server_Logon_History(EventType, PostTime, ServerName, LoginName, LoginType, SID, ClientHost, IsPooled)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)'),
EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit')
GO
Will this work?
Okay, so, to use that, I'd need to first create a table called Server_Login_History, assuming in MASTER, to contain the data the trigger generates, right?
Michael's got the database name qualified in the insert statement. DBAUtilities is probably his repository of DBA-type stuff. You can create the table and store the data wherever you want, but if you have a DBA database, it makes sense.
(slaps head) I see. I'll give this a shot and see what I end up with.
June 17, 2015 at 3:00 pm
Okay... here's where I am...
Table create script works fine...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Server_Logon_History](
[EventType] [nvarchar](100) NULL,
[PostTime] [datetime] NULL,
[LoginName] [nvarchar] (512) NULL,
[ClientHost] [nvarchar] (512) NULL,
[DatabaseName] [varchar](512) NULL
) ON [PRIMARY]
GO
Trigger create works fine...
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)'),
EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(512)')
GO
Since my primary need is to know when the actual databases have been accessed, I added DatabaseName to the mix, and removed fields I do not need. I also added the EXECUTE AS 'sa', because without it, none of the SQL Authentication connections worked. With it, Windows and SQL Authentication worked.
The table populates, but the DatabaseName column is always NULL.
EventTypePostTime LoginName ClientHostDatabaseName
LOGON2015-06-17 16:36:57.863NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:07.890NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:17.893NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:22.403(hidden) (hidden) NULL
LOGON2015-06-17 16:37:25.680(hidden) (hidden) NULL
LOGON2015-06-17 16:37:25.940NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:26.033NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:27.640(hidden) (hidden) NULL
LOGON2015-06-17 16:37:27.900NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:37.917NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:47.930NT SERVICE\ReportServer<local machine>NULL
LOGON2015-06-17 16:37:56.517(hidden) <local machine>NULL
(hidden) is just me being anal about internal security 😀
I can just turn off Reporting Service, as we don't use it.
So... two questions...
A... Any ideas about the DatabaseName column? Did I do something wrong?
B... Is there a way, based on the PostTime column, or a different column which could be added, to record only the most recent logon? For example, if FRED logs in 3 days in a row, I'd like only the 3rd day showing in the table.
Thanks guys... you're being very helpful. I appreciate it.
June 17, 2015 at 7:19 pm
Sorry, I saw this post and ran out the door after I published the code.
1. I have a DBAUtilities database on all my servers. This holds auditing tables, Ola Hallengrin's scripts, a calendar table, a tally table, disk usage tables, etc. etc.
I rarely put anything into master. I tend to leave that alone. By putting these thing into a separate database, moving a server or creating a new server is a bit easier, I never have to worry about objects in system databases, this one just gets moved with the rest of them. Plus, you do not have to grant permissions in the master database to access these objects.
2. I still hate triggers Ed.:w00t:
3. I don't think Database name is part of the EventData Function. Try this:
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
4.
B... Is there a way, based on the PostTime column, or a different column which could be added, to record only the most recent logon? For example, if FRED logs in 3 days in a row, I'd like only the 3rd day showing in the table.
Not with this method. Again, I still hate triggers because people try to put too much logic in them. This is designed to capture logins. That's it. KISS.
Write a query that displays the data in the manner you want. Lot's more flexibility there!
5. I used this as a test to see if we were using connection pooling properly. It's not been used for a while.
I seem to remember having issues with EXECUTE AS sa. Of course, at my advanced age, remembering things is tough.
I do remember granting the rights for this to run properly.
6. We have a pretty heavily used system. This table got very big in a very short period. I ended up creating a series of summary tables, and filling them up on a nightly basis. I then deleted the records from this table.
7. I've attached lots of code. It may or may not work!!!! But it will give you a big head start.
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 17, 2015 at 10:47 pm
mark.dickinson (6/17/2015)
B... Is there a way, based on the PostTime column, or a different column which could be added, to record only the most recent logon? For example, if FRED logs in 3 days in a row, I'd like only the 3rd day showing in the table.
You have to trust in an old geezer that's seen dozen's of people make such a mistake. Don't even think about doing that. You'll absolutely hate yourself when the requirements change down the line. Either log facts as they are or forget about logging all together.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2015 at 5:30 am
Michael L John (6/17/2015)
Sorry, I saw this post and ran out the door after I published the code.1. I have a DBAUtilities database on all my servers. This holds auditing tables, Ola Hallengrin's scripts, a calendar table, a tally table, disk usage tables, etc. etc.
I rarely put anything into master. I tend to leave that alone. By putting these thing into a separate database, moving a server or creating a new server is a bit easier, I never have to worry about objects in system databases, this one just gets moved with the rest of them. Plus, you do not have to grant permissions in the master database to access these objects.
2. I still hate triggers Ed.:w00t:
1. Mark, if you don't have a database of DBA-type stuff, give it some thought. Don't give anyone other than the DBAs permission to it and collect whatever you want. It can capture and store whatever data you need. Just don't let it get too out of control. It keeps master cleaned and the procedures and data you keep are isolated from the rest of the world.
I have a separate database for tally tables, general-purpose functions, globally-applicable tables, etc. that I expose to the world. Having this separate is a matter of preference to keep global stuff global and DBA stuff isolated.
2. Michael, I'm glad to see you still have your distaste for triggers.
June 18, 2015 at 5:31 am
Jeff Moden (6/17/2015)
mark.dickinson (6/17/2015)
B... Is there a way, based on the PostTime column, or a different column which could be added, to record only the most recent logon? For example, if FRED logs in 3 days in a row, I'd like only the 3rd day showing in the table.You have to trust in an old geezer that's seen dozen's of people make such a mistake. Don't even think about doing that. You'll absolutely hate yourself when the requirements change down the line. Either log facts as they are or forget about logging all together.
Let me second what Jeff said about capturing the data. Log the facts. You can report on them however you want - first time per day, last time per day, all times per user over multiple days or however it's needed. Without the data, though, you can't report on it. Then, when the requirements change, you can change the report without changing the capture.
June 18, 2015 at 6:13 am
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!
June 18, 2015 at 7:38 am
1. Mark, if you don't have a database of DBA-type stuff, give it some thought. Don't give anyone other than the DBAs permission to it and collect whatever you want. It can capture and store whatever data you need. Just don't let it get too out of control. It keeps master cleaned and the procedures and data you keep are isolated from the rest of the world.
I have a separate database for tally tables, general-purpose functions, globally-applicable tables, etc. that I expose to the world. Having this separate is a matter of preference to keep global stuff global and DBA stuff isolated.
Sounds like an article or a SSUG presentation
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/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply