How to Create Login IDs

When you install SQL Server, it’s configured with some built-in IDs. Then, to add additional login IDs, you can use the CREATE LOGIN statement shown below. As you can see, the syntax you use depends on whether you’re creating a login for Windows authentication or SQL Server authentication.

To create a new Windows login ID, you can simply specify the login name and the FROM WINDOWS keywords. Then, a login ID with the same name as the Windows login ID is created. Note that the name you specify must include the Windows domain name along with the user or group name, and the name must be enclosed in square brackets. The first CREATE LOGIN statement below, for example, creates a login ID for a Windows user named w3comp1 in the Windows domain named Accounting.

If you’re working with SQL Server Express on your own system, the domain name is just the name of your computer. In that case, though, you probably won’t need to set up additional login IDs. The exception is if you have more than one user account defined on your system, in which case you can set up a separate login for each account.

In addition to the login name, you can specify the default database and language. If you set the default database, the user won’t have to execute a USE statement to work with that database. If you don’t specify a default database, the system database named master is the default.

To create a new SQL Server login ID, you must specify a login name and password. The second statement below, for example, creates a login ID for user w3comp2 with the password ” pt8806FG$B”. It also sets the default database to AP.

The last two options determine how password policies are enforced. If the CHECK_EXPIRATION option is on, users are reminded to change passwords, and SQL Server disables IDs that have expired passwords. If the CHECK_POLICY option is on, password policies are enforced, and the CHECK_EXPIRATION option is also on unless it’s explicitly turned off.

In most cases, you’ll leave these options at their defaults so the password policies specified for the server are enforced. Among other things, the default password policies for SQL Server 2012 require that SQL Server logins use strong passwords, which are difficult for someone to guess. This section lists the guidelines for coding strong passwords, and the example in this section that creates a SQL Server login ID illustrates a strong password.

Another option you can use with the CREATE LOGIN statement is MUST_CHANGE. If you include this option, the user will be prompted for a new password the first time the new login is used. That way, users can set their own passwords. If you specify the MUST_CHANGE option, the CHECK_EXPIRATION and CHECK_POLICY options must also be on.

By the way, you should know that the CREATE LOGIN statement, as well as many of the other statements presented in this chapter, were introduced with SQL Server 2005. These statements replace stored procedures that were used in previous versions of SQL Server. For example, you used the sp_AddLogin stored procedure to add a new SQL Server login ID. Although these stored procedures are still supported by SQL Server 2012, they will be dropped in a future release of SQL Server. So you should use the statements presented in this chapter instead of the equivalent stored procedures.

The syntax of the CREATE LOGIN statement

For Windows authentication

 For SQL Server authentication

 

A statement that creates a new login ID from a Windows account

 

A statement that creates a new SQL Server login ID

 

Guidelines for strong passwords

  • Cannot be blank or null or the values “Password”, “Admin”, “Administrator” , “sa”, or “sysadmin”
  • Cannot be the name of the current user or the machine name
  • Must contain more than 8 characters
  • Must contain at least three of the following: uppercase letters, lowercase letters, numbers, and non-alphanumeric characters (#, %, &, etc.)

 

Description

  • You use the CREATE LOGIN statement to create a new SQL Server login ID or to create a new login ID from a Windows account. This statement was introduced with SQL Server 2005.
  • If you don’t specify a default database when you create a login, the default is set to master. If you don’t specify a default language, the default is set to the default language of the server. Unless it’s been changed, the server language default is English.
  • The password you specify for a SQL Server login ID should be a strong password. A strong password is not easy to guess and cannot easily be hacked. A password can have up to 128 characters.
  • If you include the MUST_CHANGE option, SQL Server will prompt the user for a new password the first time the login ID is used.
  • The CHECK_EXPIRATION option determines whether SQL Server enforces password expiration policy. The CHECK_POLICY option determines if password policies, such as strong passwords, are enforced. These options are enforced only if SQL Server is running on Windows Server 2003 or later.