We want to give provision for SQL Authentication users to change their password for the first time and password should never expire and the user should be changing only his password and not others password. I have done some research and tried to implement the same functionality and it was working fine. However, I came to know that I will not be able to use 'MUST_CHANGE' option and CHECK_POLICY=ON with my implementation. For me, MUST_CHANGE is a must. Is there a way, using triggers or so we can implement this functionality. Did anyone done this before, if so, please let me know how can I accomplish it.
-- Step1 : Login as admin user and then create a login with check_policy = off
CREATE LOGIN [Smith]
-- Step2 : Provide connect permission to "Smith" login
GRANT CONNECT SQL TO Smith;
-- Step3 : Open a new connection in SSMS and login as "Smith" user
-- and she can able to change her password
ALTER LOGIN Smith
WITH PASSWORD = 'Helloworld$123' OLD_PASSWORD = 'Test#123';
-- Step 4: logoff and re-connect as "Smith" using new Password='Helloworld$123'
Alternate way :
Granting ALTER ANY LOGIN permission to Smith.
However, Smith has permissions to change other login's passwords as well which shouldn’t be the case.
- This topic was modified 2 months, 1 week ago by vsamantha35.