Login Trigger / Audit

Question

Login Trigger / Audit

jkw

SSC Eights!

Points: 815
More actions
July 31, 2019 at 12:49 pm

#3667521

I'm getting ready to move our current SQL server data, etc. to a new server. The current system has a bunch of logins going back years due to poor maintenance and it's unknown if a login is still being used. I know can get the last login times but that only returns a small handful of what is out there. I've considered bringing over the ones I know are being used and apply whoever screams that they cannot login method for the unknown. However, I was wondering if there was a way to run an trigger or audit and only log distinct logins instead of logging every time a login is used. That way I don't generate a large login file since this is a very active system. Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andrey SSC Eights! Points: 840 More actions · Answer 1

I would get list containing current connected, extracted from default trace and copy them to the new server enabled, the rest I would copy disabled .

When the new server is in prod, I would monitor failed login events (default trace) and enable them on the fly.

" log distinct logins instead of logging every time a login is used.... this is a very active system."

If "very active system" means very high level of logon events, it would be rather consuming exercise because the process should check newcomer with the list of known logins and decide whether to log it or not..

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Not to mention that the default trace file has a pretty small row limit before it "rolls over", it only stores (IIRC) 6 total files, and a busy system can chew through all six in just a couple of minutes and so it would need to be aggressively sampled in order to do the Pokemon thing and "catch them all".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrey SSC Eights! Points: 840 More actions · Answer 3

Jeff, I agree that on busy systems these 5 files (20 MB each) of default trace will be overwritten soon, but look at at from the following angle:

it's available out of the box and you don't need to configure anything except "default trace enabled"
depending on a workload and events stream, to check failed logins once an hour or twice a day can be enough

I mentioned this source of information assuming that in general, default trace files can contain data for 1-3 days or even more. We don't know what Jon.Wilson has on the plate.

jkw SSC Eights! Points: 815 More actions · Answer 4

Thanks for your input. I've done some thinking about this and might be able to work something out querying the sys.syslogins. Since a trigger will catch every login it's not really the way I want to go, but could over time give me what I'm looking for. I'm just trying to find out what logins are being used. I'm thinking of just creating a SSIS package that I can schedule to fire off and query the sys.syslogins and write the info out to a table. I should also be able to narrow it down to on entry per login and only add to the table when a new record is found. I could have this run over a few weeks to get a better picture of what is being used.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Andrey wrote:

Jeff, I agree that on busy systems these 5 files (20 MB each) of default trace will be overwritten soon, but look at at from the following angle:

<li style="list-style-type: none;">
it's available out of the box and you don't need to configure anything except "default trace enabled"

<li style="list-style-type: none;">
depending on a workload and events stream, to check failed logins once an hour or twice a day can be enough

I mentioned this source of information assuming that in general, default trace files can contain data for 1-3 days or even more. We don't know what Jon.Wilson has on the plate.

Totally agree with everything you say, especially the part about "we don't know" and that's why I wanted the OP to know that a busy system (like mind) will rip through the default trace files in just minutes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

jkw wrote:

Thanks for your input. I've done some thinking about this and might be able to work something out querying the sys.syslogins. Since a trigger will catch every login it's not really the way I want to go, but could over time give me what I'm looking for. I'm just trying to find out what logins are being used. I'm thinking of just creating a SSIS package that I can schedule to fire off and query the sys.syslogins and write the info out to a table. I should also be able to narrow it down to on entry per login and only add to the table when a new record is found. I could have this run over a few weeks to get a better picture of what is being used.

You should probably first start with sp_ValidateLogins to check for Active Directory logins (Windows users and groups) that no longer exist.

https://docs.microsoft.com/en-us/SQL/relational-databases/system-stored-procedures/sp-validatelogins-transact-sql?view=sql-server-linux-ver15

If you have a lot of users that login via an SQL Server login, you should fix that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jkw SSC Eights! Points: 815 More actions · Answer 7

Thanks I'll look into that. I agree with you regarding the SQL Server logins and that's what I'm pushing since there are quite a few of them. I'd like to eliminate password maintenance on my end and let the AD team do their thing.