You are your company’s SQL administrator. A SQL Server 2008 computer named SQL_main contains adatabase named Finance.
The Finance database is used by the accounting department to manage yourcompany’s accounts payable and accounts receivable.
All users use SQL logins to access this database.Password expiration is not enabled by default.
A user named Amy in the accounting department took a medical leave of absence and has just returned.
Duringthat time, her SQL login, Amy16 , was disabled. She has forgotten her password. She contacts you requestingthat you change her password.
However, you must ensure that your password change is only temporary and thatshe resets the password at login.
Which Transact-SQL statement should you use first?

A.
ALTER LOGIN Amy16 ENABLE WITH PASSWORD=’@Bc12345′ MUST_CHANGE;

B.
ALTER LOGIN Amy16 ENABLE WITH PASSWORD=’@Bc12345′ MUST_CHANGE CHECK_POLICY=ONCHECK_EXPIRATION=ON;

C.
ALTER LOGIN Amy16 ENABLE;

D.
ALTER LOGIN Amy16 WITH PASSWORD=’@Bc12345′ MUST_CHANGE;

E.
ALTER LOGIN Amy16 WITH CHECK_POLICY=ON, CHECK_EXPIRATION=ON;

Explanation:

You should use the following Transact-SQL statement first: ALTER LOGIN Amy16 ENABLE;
You must first enable the user account.
You should not use the following Transact-SQL statement:
ALTER LOGIN Amy16 ENABLE WITH PASSWORD=’@Bc12345′ MUST_CHANGE;
You cannot reset the password until after the user account is enabled. In addition, you cannot issue the MUST_CHANGE parameter until after the password policies are enabled.
You should not use the following Transact-SQL statement:
ALTER LOGIN Amy16 ENABLE WITH PASSWORD=’@Bc12345′ MUST_CHANGE CHECK_POLICY=ONCHECK_EXPIRATION=ON;
You cannot reset the password until after the user account is enabled. In addition, you cannot issue the MUST_CHANGE parameter until after the password policies are enabled. Finally, you cannot enable thepassword policies until after the user account is enabled. This command will return a syntax error.
You should not use the following Transact-SQL statement:
ALTER LOGIN Amy16 WITH PASSWORD=’@Bc12345′ MUST_CHANGE;
This step should be completed after the account is enabled and after the password policies areenabled. It will be the third step in the process to completing the scenario requirements.
You should not use the following Transact-SQL statement:
ALTER LOGIN Amy16 WITH CHECK_POLICY=ON, CHECK_EXPIRATION=ON;
This step should be completed after the account is enabled. It will be the second step in the process tocompleting the scenario requirements.
The ALTER LOGIN statement supports the following arguments:
? ENABLE / DISABLE – Enables or disables the SQL login.
? PASSWORD= ‘password’ – Configures the value for the password.
? MUST_CHANGE – Forces the user to reset the password the next time the SQL login is used.
? CHECK_EXPIRATION={ON/OFF} – Specifies whether the password expiration policy is enforced. TheOFF setting is the default.
? CHECK_POLICY={ON/OFF} – Specifies whether the Windows password policies are enforced.
The ON setting is the default. To reset a bad password count, issue the ALTER LOGIN statement with the CHECK_POLICY = OFF argument, followed by another
ALTER LOGIN statement with the
CHECK_POLICY = ON argument. When this setting is
OFF , the CHECK_EXPIRATION argument is also set to OFF .

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 > Technical Reference > Transact-SQLReference > ALTER LOGIN (Transact-SQL)