April 1, 2021 at 5:58 am
Hi All,
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
USE [master]
GO
CREATE LOGIN [Smith]
WITH PASSWORD=N'Test#123',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
-- Step2 : Provide connect permission to "Smith" login
use [master]
GO
GRANT CONNECT SQL TO Smith;
GO
-- 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';
GO
-- 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.
Thank you.
Regards,
Sam
April 2, 2021 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 3, 2021 at 2:49 am
Any suggestions please.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply