Audit script for sqlserver to include: logins, permissions, last login date, last password change

Question

Audit script for sqlserver to include: logins, permissions, last login date, last password change

caz100

SSCommitted

Points: 1827
More actions
August 9, 2017 at 9:44 am

#344065

Hi
I am looking for an Audit script for sqlserver to include: logins, permissions, last login date, last password change for ALL user logins on ALL databases within a sqlserver instance.
Can anyone assist?
Many thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

caz100 - Wednesday, August 9, 2017 9:44 AM
Hi
I am looking for an Audit script for sqlserver to include: logins, permissions, last login date, last password change for ALL user logins on ALL databases within a sqlserver instance.
Can anyone assist?
Many thanks

SQL Server doesn't store last login date. There is a modified data for server principals but that doesn't necessarily mean a password change.
SQL Server does not have anything about passwords for Windows accounts - you need to get that from AD.
On this site, you can search on permissions then select scripts and see what comes closest to what you are looking for:
Search Permissions
You can also try this script and see if it meets some of your needs:
Get logins, databases users/roles and object level permission (T-SQL)

Sue

caz100 SSCommitted Points: 1827 More actions · Answer 2

Thanks Sue for the script links. For auditing purposes I am interested in the sql authenticated accounts not just the Windows AD ones.

Joe Torre SSChampion Points: 10248 More actions · Answer 3

To audit logins in SSMS right click the server and select properties on the security page select the option button to indicate your preference.

caz100 SSCommitted Points: 1827 More actions · Answer 4

Thanks Ten but this not what I need....going forward it's an idea but this is retrospective data I require.

I need a list of sql logins of all dbas within an instance, their permissions, when they last logged on and when they last changed their password.

I think sue is right sql server doesn't store when the password was last changed.

The purpose of the info is to delete unnecessary old logins....or ensure sql logins in use will change their password in the future by highlighting they haven't been changed in the last 6 months.

caz100 SSCommitted Points: 1827 More actions · Answer 5

For the last password change I found this which seems to work Sue:

-- Show all logins where the password is over 60 days old

SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'FROM sys.sql_loginsWHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE());

;

link:
https://www.mssqltips.com/sqlservertip/2379/auditing-sql-server-password-age/

So if I could some how join this information to details of when the user logged on last it would be great.

caz100 SSCommitted Points: 1827 More actions · Answer 6

To only specify enabled accounts:

SELECT name, create_date, is_disabled, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE())
and is_disabled=0;

Sue_H SSC Guru Points: 90860 More actions · Answer 7

Yup...sorry about that - forgot about that function. Thanks for the reminder - just never use it.
I did look at another script on that site that has a similar query but might give you other things to think about such as locked out accounts, expired or soon to be expired, etc.
It might be worth looking at to see the other things being checked:
SQL Server Login Management using LOGINPROPERTY function

Sue

caz100 SSCommitted Points: 1827 More actions · Answer 8

This was what I was looking for minus the permissions...

Select @@servername as SQLInstance, name as Login, LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetTime, type, type_desc, is_disabled, create_date, modify_date
from sys.server_principals
order by name;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Unless you have some custom auditing, SQL doesn't track when someone last logged on. If you need that, you'll have to set something up. An extended event session will probably work best.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

caz100 SSCommitted Points: 1827 More actions · Answer 10

Will this not work to a certain extent also?

SELECT MAX(login_time) AS [Last LoginTime], login_name [Login]

FROM sys.dm_exec_sessions
where login_name-

GROUP BY login_name;

Sue_H SSC Guru Points: 90860 More actions · Answer 11

caz100 - Wednesday, August 16, 2017 5:38 AM
Will this not work to a certain extent also?
SELECT MAX(login_time) AS [Last LoginTime], login_name [Login]
FROM sys.dm_exec_sessions
where login_name-
GROUP BY login_name;

You could at any time just go in and see what logins are connected. But that doesn't really tell you what SQL logins have not logged in unless you are continually polling this information and never miss a login. If you were going to do that, it probably makes more sense to do what Joe suggested earlier and audit all logins as this would ensure you don't miss any logins, wouldn't require writing and testing your own process to do the same, etc.

Sue

richardmgreen1 SSChampion Points: 10351 More actions · Answer 12

Hi all

I can vaguely remember setting something for last login in date but it does mean 2 things:-
1) You need to switch on logging for successful logins as well as failed (see the screenshot from Joe Torre above)
2) Once that is done, you need to parse the logs to find the relevant line and grab the date/time from that.

I do remember it wasn't easy but I did manage to get it to work (eventually!)