August 9, 2017 at 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
August 9, 2017 at 11:41 am
caz100 - Wednesday, August 9, 2017 9:44 AMHi
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
August 9, 2017 at 1:17 pm
Thanks Sue for the script links. For auditing purposes I am interested in the sql authenticated accounts not just the Windows AD ones.
August 9, 2017 at 2:29 pm
To audit logins in SSMS right click the server and select properties on the security page select the option button to indicate your preference.
August 9, 2017 at 2:39 pm
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.
August 10, 2017 at 2:49 am
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.
August 10, 2017 at 3:31 am
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;
August 10, 2017 at 7:49 am
Sue
August 16, 2017 at 3:06 am
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;
August 16, 2017 at 3:36 am
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
August 16, 2017 at 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;
August 16, 2017 at 7:20 am
caz100 - Wednesday, August 16, 2017 5:38 AMWill 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
August 18, 2017 at 2:27 am
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!)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply