DRAG DROP
You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014 Instance, with
multiple databases used for reporting.
You have recently hired a junior database administrator. You want this person to be able to view the database
structures on the server, but you do not want him or her to be able to make changes or see the data in the
tables.
The new hire’s login credentials are as follows:
Login name: JFree
Password: Jx672$qse
You want the new hire to be required to change his password on his next login.
The code that is produced should execute no matter the initial database context in which it is started.
You need to write the code required to give the new hire only the desired access, using the smallest number of
steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may
not need all of the code blocks.
Select and Place:
Answer: 
Explanation:
Note:
* MUST_CHANGE
Applies to: SQL Server 2008 through SQL Server 2014.
Applies to SQL Server logins only. If this option is included, SQL Server prompts the user for a new password
the first time the new login is used.
* The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is
granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example,
a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in
the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user
cannot read data from the table.
References:
CREATE LOGIN (Transact-SQL)
ALTER SERVER ROLE (Transact-SQL)
USE [master];
CREATE LOGIN [JFree] WITH Password = ‘Jx672$qse’ MUST_CHANGE, CHECK_EXPIRATION = ON;
GRANT VIEW ANY DEFINITION TO [JFree];
GRANT CONNECT ANY DATABASE TO [JFree];
(There is no requirement to add [JFree] to the securityadmin-role)
https://technet.microsoft.com/en-us/library/ms189610(v=sql.90).aspx
securityadmin
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.
https://technet.microsoft.com/en-us/library/ms175808(v=sql.105).aspx
VIEW DEFINITION Permission
The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. For more information about viewing metadata, see GRANT (Transact-SQL).
The VIEW DEFINITION permission can be granted on the following levels:
Server scope
Database scope
Schema scope
Individual entities
Required code is:
USE [master];
CREATE LOGIN [JFree] WITH Password = ‘Jx672$qse’ MUST_CHANGE,CHECK_EXPIRATION = ON;
GRANT VIEW ANY DEFINITION TO [JFree];
GRANT CONNECT ANY DATABASE TO [JFree];