none
How to prompt a user to change his SQL account password?

    Question

  • Hi there

    Recently my DBA reset my SQL account password. What does he have to do OR I have to do to change my password now?

    With domain accounts you can set it at the Domain Controllers and the user will be prompted/asked to change his password to a new one.

    Thank you

     


    JCD
    Wednesday, April 20, 2011 3:36 PM

Answers

  • Hi JCD, To force a user to change password, the sql login can be created with "Enforce Password policy" You can also set it to force the user change it in next login. Password policy can be configured for each sql login.

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c0040c0a-a18f-45b9-9c40-0625685649b1.htm

    ALTER LOGIN Command can be use to configure password policy of SQL Logins,

    USE [master]
    
    
    
    GO
    
    
    
    CREATE LOGIN [prompt] WITH PASSWORD=N'XXXXX' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
    
    
    
    GO
    
    
    
    
    
    
    
    

     

    To manually change the password DBA/admin can use the ALTER login command

    USE [master]
    
    
    
    GO
    
    
    
    ALTER LOGIN [prompt] WITH PASSWORD=N'XXXX'GO
    
    
    
    

    To Change an existing login and enforce password policy can also use ALTER login

    USE [master]
    GO
    ALTER LOGIN [prompt] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
    GO

     

    Thanks

    Jay



    • Edited by SQL_Jay Wednesday, April 20, 2011 4:51 PM added code
    • Marked as answer by Maximusdm Thursday, April 21, 2011 11:01 PM
    Wednesday, April 20, 2011 4:36 PM

All replies

  • Hi,

    it depends of the version of SQL Server and the settings for the SQL Account. For SQL 2005+ only and in case the flag "User must change password at next login", you will have to change the pwd.

    Regards,

    Stef

    Wednesday, April 20, 2011 4:32 PM
  • Only the SQL Server DBA (or someone with sufficient rights) can change a user password. Active Domain security is another matter. Users can change their own passwords (if the domain admin permits it).

    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, April 20, 2011 4:33 PM
  • Hi JCD, To force a user to change password, the sql login can be created with "Enforce Password policy" You can also set it to force the user change it in next login. Password policy can be configured for each sql login.

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c0040c0a-a18f-45b9-9c40-0625685649b1.htm

    ALTER LOGIN Command can be use to configure password policy of SQL Logins,

    USE [master]
    
    
    
    GO
    
    
    
    CREATE LOGIN [prompt] WITH PASSWORD=N'XXXXX' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
    
    
    
    GO
    
    
    
    
    
    
    
    

     

    To manually change the password DBA/admin can use the ALTER login command

    USE [master]
    
    
    
    GO
    
    
    
    ALTER LOGIN [prompt] WITH PASSWORD=N'XXXX'GO
    
    
    
    

    To Change an existing login and enforce password policy can also use ALTER login

    USE [master]
    GO
    ALTER LOGIN [prompt] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
    GO

     

    Thanks

    Jay



    • Edited by SQL_Jay Wednesday, April 20, 2011 4:51 PM added code
    • Marked as answer by Maximusdm Thursday, April 21, 2011 11:01 PM
    Wednesday, April 20, 2011 4:36 PM
  • SQL Server is a back-end process. It cannot show a dialog bax on a client machine. I.e., your application need to have this functionality, so your users can have a GUI to change the password (assuming this is what you ask). You can of course also write a tiny dedicated app for this.
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, April 21, 2011 8:06 AM
  • Hi,

    I agree with Tibor.

    By default, SQL Server Management Studio has such functionality that allow you to update password if "Enforce password expiration" or "User must change password at next login" has been enabled for your SQL account, you are prompted to input new password after password expired or during the initial logon process.

    However, if you SQL account password was changed by your DBA, you need to know that new password in order to connect to SQL Server, otherwise, you will get a login failed error message (user name and password do not match).

    Hope this helps.

     

     


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, April 21, 2011 8:22 AM
    Moderator
  • thanks! that worked!
    JCD
    Thursday, April 21, 2011 11:01 PM