SQL SERVER – Change Password at the First Login

MUST_CHANGE requires a SQL-authenticated login to change password at its first connection. Policy options and the client workflow must support it.

-- Administrative template only. Supply a securely generated temporary secret.
-- CREATE LOGIN [YourLogin]
-- WITH PASSWORD=N'<secure temporary secret>' MUST_CHANGE,
--      CHECK_POLICY=ON, CHECK_EXPIRATION=ON;

MUST_CHANGE requires both CHECK_POLICY and CHECK_EXPIRATION to be ON. My old example explicitly set only expiration. Showing both clarifies the requirement. The template avoids the previously published example password.

Generate and deliver the temporary secret securely. The client must support the password-change response. An unattended service connection can fail instead of displaying a prompt. Windows-authenticated identities use their Windows password process.

This commented template does not create an account. Confirm authentication, permissions and onboarding requirements. Verify first-use and subsequent connections in a controlled test.

Reference: First-login password policy requirements.

Related reading

• SQL in Sixty Seconds video
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

A temporary password is not complete onboarding, it is a secret within a supported first-use identity workflow.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.