User must change password at next login

Question

User must change password at next login

RayMan007

SSChasing Mays

Points: 639
More actions
April 17, 2017 at 11:01 am

#104389

I am having a little trouble in trying to find a way to ignore new SQL accounts that have to change their password for the first time. The script below works fine to turn off the "enforce password expiration" however for accounts that have the "User must change password at next login" enabled it fails. The SQL accounts only have read permissions and are created through an API which is outside my control.
My question, is there a way to ignore the new SQL accounts the have the "User must change password at next login" enabled using this script?
ALTER PROCEDURE [dbo].[UpdatePolicy]
AS
DECLARE @PolicyUpdate NVarchar(4000) = ''
SELECT @PolicyUpdate = @PolicyUpdate + 'ALTER LOGIN ' + '[' + name + '] ' + ' WITH CHECK_EXPIRATION = OFF; '
FROM sys.sql_logins
WHERE is_policy_checked = 1
AND is_expiration_checked = 1;
EXEC (@PolicyUpdate)

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ronzo Hall of Fame Points: 3917 More actions · Answer 1

How about wrapping the ALTER LOGIN in a TRY...CATCH? That would at least allow your policy update to continue on with the rest of the users.

Have Fun!
Ronzo

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

You can use LOGINPROPERTY(name, 'IsMustChange') to check whether the password has to be changed.

Please, please, please, though.... take an afternoon (or however long it takes) to read about SQL injection, and then put some safeguards in your stored procedure, otherwise you're going to get bitten very hard. It may not happen this week, or even this year, but it will happen.

John

Thom A SSC Guru Points: 99126 More actions · Answer 3

You do also realise that the above SQL is only going to update 1 account right? You can't assign multiple values to a variable, but that seems to be what you are trying to achieve.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Thom A - Tuesday, April 18, 2017 3:13 AM
You do also realise that the above SQL is only going to update 1 account right? You can't assign multiple values to a variable, but that seems to be what you are trying to achieve.

No, the code is concatenating all the values into one.
Test code:
DECLARE @PolicyUpdate NVarchar(4000) = '';

SELECT @PolicyUpdate = @PolicyUpdate + 'ALTER LOGIN ' + QUOTENAME( name) + ' WITH CHECK_EXPIRATION = OFF; ' + CHAR(10) FROM sys.sql_logins --WHERE is_policy_checked = 1 --AND is_expiration_checked = 1;

PRINT @PolicyUpdate;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99126 More actions · Answer 5

Luis Cazares - Tuesday, April 18, 2017 6:30 AM
Thom A - Tuesday, April 18, 2017 3:13 AM
You do also realise that the above SQL is only going to update 1 account right? You can't assign multiple values to a variable, but that seems to be what you are trying to achieve.
No, the code is concatenating all the values into one.
Test code:
DECLARE @PolicyUpdate NVarchar(4000) = '';
SELECT @PolicyUpdate = @PolicyUpdate + 'ALTER LOGIN ' + QUOTENAME( name) + ' WITH CHECK_EXPIRATION = OFF; ' + CHAR(10) FROM sys.sql_logins --WHERE is_policy_checked = 1 --AND is_expiration_checked = 1;
PRINT @PolicyUpdate;

My Bad! Brain has obviously not got into gear after the holidays yet ^_^

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk