You are the SQL administrator for your company. You have installed SQL Server 2008 on two Windows Server2008 computers.
You create a new database named Research for the research department on the SQL Server2008 instance named SQL_production.

You have been asked to ensure that all SQL logins created for the research department employees meet thefollowing criteria:

* The logins should be able to issue commands within the Research database without specifically includingthe database name in the command.
* The Windows Server 2008 password policies and password expiration should be enforced for the SQLlogins.
* The account should create a temporary password and force the user to reset the password.

You decide to create a template account to use to create all the research department accounts.
You plan tocreate a CREATE LOGIN script from the template account for future use.
You open the SQL Server Management Studio, and open the SQL_production instance.
Then you right-click the Security folder, point to New , and select Login.
On the General page, you type Template in the Login namebox.

What else should you do? (Choose all that apply. Each correct answer represents part of the solution.)

A.
On the General page, select the SQL Server authentication option.

B.
On the General page, select the Windows authentication option.

C.
On the General page, enter and confirm the temporary password in the appropriate fields.

D.
On the General page, select the Enforce password policy , Enforce password expiration, and User mustchange password at next login check boxes.

E.
On the General page, change the Default database option to Research.

F.
On the General page, change the Default database option to msdb.

G.
On the Status page, select Enabled.

H.
On the Status page, select Disabled.

Explanation:

On the General page, you should complete the following steps:1. Select the SQL Server authentication option. This option configures the server to use SQL authentication2. Enter and confirm the temporary password in the appropriate fields. This will create a temporary passwordfor the template account.3. Select the Enforce password policy , Enforce password expiration , and User must change passwordat next login check boxes. This will ensure that the Windows Server 2008 password policies andpassword expiration are enforced for the SQL logins.4. Change the Default database option to Research . This will ensure that the research department employeesare able to issue commands within the Research
database without specifically including the database name inthe command. The General page of the Login – New dialog box is shown in the following graphic:

5. Finally, you should select
Disabled
on the
Status
page because the account you are creating is a templateaccount. You should not specifically enable template accounts.You should not select the
Windows authentication
option on the
General
page. You need to use SQLauthentication for SQL logins. SQL authentication is mainly used for backward compatibility. Using WindowsAuthentication is recommended because it provides better security.You should not change the
Default Database
option to
msdb
. This scenario specifically stated that you want thelogins to be able to issue commands within the
Research
database without specifically including the databasename in the command. Therefore, the default database should be the
Research
database, not
msdb
.You should not select
Enabled
on the
Status
page. Because you are creating a template account, you shoulddisable the account. You do not want the template account to become a possible security issue later. In addition,if you enabled the account, a user could log in with the account, change its password at login, and have access tothe database.

All of these settings can be configured or changed using the
CREATE LOGIN
or
ALTER LOGIN
statement.After creating the template account, you could create a
CREATE LOGIN
script from the template account. The
CREATE LOGIN
script created from the template account with the settings for this scenario would be as follows:
CREATE LOGIN [Template] WITH PASSWORD=N’@Bc12345′, DEFAULT_DATABASE=[Research],DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER LOGIN [Template] DISABLE
GO
You should change the login name and the status to use this script to create the account.
Objective:
Managing SQL Server Security
Sub-Objective:
Manage logins and server roles.
References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity andAccess Control > Principals > Managing Logins and Users How-to Topics > How To: Create a SQL Server Login