Login creation

  • 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

    • This topic was modified 2 years, 11 months ago by  vsamantha35.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • 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