70-462 Free Practice Test

- (Exam Topic 1)
You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA.
You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table.
You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.
Which Transact-SQL statement should you use?

1. A. GRANT DELETE ON Purchase
2. B. Suppliers TC ContosoUser
3. C. CREATE PROCEDURE Purchases.PurgeInactiveSuppliersWITH EXECUTE AS USER = \'dbo\'ASDELETEFROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
4. D. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
5. E. CREATE PROCEDURE Purchase
6. F. PurgeInactiveSuppliersASDELETEFROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchase
7. G. PurgeInactiveSuppliers TO ContosoUser

Correct Answer: D
References:
http://msdn.microsoft.com/en-us/library/ms188354.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx

- (Exam Topic 5)
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages. What should you do?

1. A. In the Database Mail Configuration Wizard, create an email account for each recipient\'s email address in the Database Mail profile.
2. B. Configure the AgentPortalUser user as a member of SQLAgentOperationRole in the msdb database.
3. C. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser
4. D. Enable the Guest user in the msdb database.

Correct Answer: C
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

- (Exam Topic 4)
In which of the following statements about audit information stored in an SQL Server 2012 environment is TRUE?

1. A. It is located in a file specified in the File Path property of the Audit.
2. B. It is located in the Audit table in the MSDB database.
3. C. Each login has an audit record, which is viewable under Logins -> Audit.
4. D. SQL 2012 does not store any audit information.

Correct Answer: A
References:
https://msdn.microsoft.com/en-us/library/cc280525(v=sql.110).aspx

- (Exam Topic 5)
You install a Microsoft SQL Server 2012 instance.
The instance will store data extracted from two databases running on Windows Azure SQL Database. You hire a data steward to perform interactive data cleansing and ad hoc querying and updating of the
database.
You need to ensure that the data steward is given the correct client tools to perform these tasks. Which set of tools should you install?

1. A. SQL Server Management Studio and Distributed Replay Client
2. B. Master Data Services and Data Quality Client
3. C. Data Quality Client and Distributed Replay Client
4. D. Data Quality Client and SQL Server Management Studio

Correct Answer: B
References: https://www.mssqltips.com/sqlservertutorial/3802/sql-server-master-data-services-architecture/

- (Exam Topic 5)
Which of the following is a valid upgrade path for SQL Server 2012?

1. A. SQL Server 2000 -> SQL Server 2012
2. B. SQL Server 2000 -> SQL Server 2005 -> SQL Server 2012
3. C. SQL Server 7 -> SQL Server 2012
4. D. Oracle 11g -> SQL Server 2012

Correct Answer: B
References:
https://technet.microsoft.com/en-us/library/ms143393(v=sql.110).aspx https://technet.microsoft.com/en-us/library/ms143393(v=sql.90).aspx