2 added 590 characters in body
source | link

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.

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.

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.

1
source | link

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.