How can I force SQL Server 2005 logins to change their passwords periodically?

Printer friendly

As you might be aware, SQL Server 2005 includes a new way to enforce password policy & expirations for SQL Server login IDs. This was not the case in previous SQL Server versions. In the past, the only option you had was to use Windows Authentication if you wanted to enforce a password policy, but this was not always possible. Many applications can't use Windows Authentication and SQL Server authentication is the only option.

If you currently are using SQL Server 2000 or earlier, and need the ability enforce a password policy on SQL Server logins, then this is a good incentive to upgrade to SQL Server 2005.

In SQL Server 2005, there are two ways to turn on a password policy for SQL Server logins. You can use either Management Studio's GUI interface, or you can use Transact-SQL.

If you want to use Management Studio to enforce password policies, when you create a new login, or if you edit an existing login, you can have to select which of the following policies you want to enforce:

• Enforce password policy
• Enforce password expiration
• User must change password at next login

If you choose one or more of the above options for each of the SQL Serve logins on your SQL Server 2005 instance, then SQL Server 2005 will enforce these rules based on the password rules already set for your domain.

If you want to use Transact-SQL to add password policy enforcement, you can do so using new options available in the CREATE LOGIN or the ALTER LOGIN commands.

Your Name
Email	(Emails will not be displayed on the site or used for promotional purposes)
Comment
Type characters in the image	(case sensitive)