Creating a SQL Server user authentication schema

Creating a SQL Server user authentication schema

How can I generate a SQL Server user authentication/authorization schema (tables/keys/indexes) to support the following requirements:

  1. A user is required to have a login name, first name, last name, and password.
  2. A user password will expire in 90 days.
  3. A user account will be locked after five failed attempts in five minutes.
  4. A locked account will automatically be unlocked 20 minutes after last failed login attempt.
  5. A user can change their password but it cannot be the same as any of the last 10 passwords used.
  6. All data changes have to be tracked and attributable to the person making the changes.

    Requires Free Membership to View

    Login

    When you register, you’ll also receive targeted emails from my team of award-winning editorial writers.  Our goal is to keep you informed on the hottest topics and biggest challenges faced by today's SQL Server database pros.

    Hannah Smalltree, Editorial Director

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

If you are using SQL Server authentication, you will have to put source code in your application to account for "1" and "2". Since SQL Server will be doing the authentication, you won't be able to perform "3" through "6". If you choose to use Windows authentication – more secure with some tradeoffs – you'll be able to set the password complexity and lockout options with a Group Policy Object or Local Security Policy.

This was first published in December 2007

Back to top