1

For the Security Audit of SQL servers 2005/8 R2,

We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via LastSetTime in Loginproperty. But for many servers there are more than 80 logins. How should we extract this information for existing logins?

Also, for those we found who have not changed password in last X days, I believe we need to have existing password. Now the problem is, that existing password cannot be found as was set up way back and password change policy was not marked. What should we do in such cases where we need to change the password of more than 80 logins in a go, as we have plenty of servers to perform this activity?

Thanks!

  • Why would you need the existing password? – Reaces Feb 9 '15 at 14:24
  • Because i am using this ALTER LOGIN yoursqllogin WITH PASSWORD = 'newpassword' OLD_PASSWORD = 'oldpassword' – KASQLDBA Feb 9 '15 at 14:30
  • If you have Control Server permissions or the sysadmin role you don't need to specify the old password. msdn documentation – Reaces Feb 9 '15 at 14:34
1

I had thought you would be able to use policy based management but couldn't find a way to get the PasswordLastSetTime property. It didn't exist in the Login facet.

However what you an do is run the following query to get the PasswordLastSetTime for all SQL Logins on the instance, then use a tool such as Idera's Multi Query or a CMS (Central Management Server) to run it across all of your instances.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime')
FROM sys.server_principals
WHERE TYPE = 'S'

Also as stated in the comments by @Reaces if you are a sysadmin or have control server then you can change the password without the old_password.

EDIT: In response to your comment you can add an additional column to generate the ALTER LOGIN command dynamically. This does of course assume that you are sysadmin and don't need the OLDPASSWORD keyword added.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime'),
    'ALTER LOGIN ' + quotename(name) + ' WITH PASSWORD = ''TemporaryPassword'' ' +
    'MUST_CHANGE, CHECK_EXPIRATION = ON '
FROM sys.server_principals
WHERE TYPE = 'S'

I've also added the MUST_CHANGE clause to force the user to change their password from your generic password the next time they log in.

  • thanks, query above list the expired logins. But say for those 80 expired logins how would i go about changing the password of all logins at once? Or do i need to do that manually one by one and reset the password for each login – KASQLDBA Feb 10 '15 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.