Microsoft 70-458 Online Practice Questions and Exam Preparation

Microsoft 70-458 Online Questions & Answers

• Question 81:

  Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

  You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named userA is a member of a role named Sales.

  UserA is granted the Select permission on the Regions table and the Sales role is granted the Select permission on the Customers schema. You need to ensure that the Sales role, including userA, is disallowed to select from the Regions table.

  A. DENY SELECT ON Object::Regions FROM Sales
  B. DENY SELECT OH Schema::Customers FROM Sales
  C. REVOKE SELECT ON Object::Regions FROM Sales
  D. REVOKE SELECT ON Schema::Customers FROM Sales
  E. DENY SELECT ON Object::Regions FROM UserA
  F. DENY SELECT ON Schema::Customers FROM UserA
  G. REVOKE SELECT ON Object::Regions FROM UserA
  H. REVOKE SELECT ON Schema::Customers FROM UserA
  I. EXEC sp_addrolemember 'Sales', 'UserA'
  J. EXEC sp_droprolemember 'Sales', 'UserA'
  A. DENY SELECT ON Object::Regions FROM Sales

• Question 82:

  You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task.

  Changes to the customer attributes must be tracked over time.

  You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.

  Which transformation should you use? (To answer, select the appropriate transformation in the answer area.)

  Hot Area:

  

  

• Question 83:

  You are developing a SQL Server Integration Services (SSIS) package. The package contains a user-defined variable named @Queue which has an initial value of 10.

  The package control flow contains many tasks that must repeat execution until the @Queue variable equals 0.

  You need to enable the tasks to be grouped together for repeat execution.

  Which item should you add to the package? (To answer, select the appropriate item in the answer area.)

  Hot Area:

  

  

• Question 84:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.

  You have added a CDC Source component to the data flow to read changed data from the source system.

  You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.

  A. Merge Join
  B. DQS Cleansing
  C. Pivot
  D. CDC Splitter
  D. CDC Splitter

• Question 85:

  You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.

  You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.

  You need to develop a data flow that imports the data while meeting the requirements.

  How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)

  Select and Place:

  

  

• Question 86:

  You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders.

  Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmented.

  You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

• Question 87:

  You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server. You need to create a server role named SpecialDBARole that can perform the following functions:

  

  View all databases.

  

  View the server state.

  

  Assign GRANT, DENY, and REVOKE permissions on logins.

  You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary.

  A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
  B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
  C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
  D. GRANT VIEW DEFINITION TO [SpecialDBARole];
  E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
  F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
  B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
  C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
  F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];

• Question 88:

  You administer a Microsoft SQL Server 2012 database that has Trustworthy set to on. You create a stored procedure that returns database-level information from Dynamic Management Views.

  You grant User1 access to execute the stored procedure.

  You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.

  A. Grant the db_datareader role on the database to User1.
  B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
  C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
  D. Move the stored procedure to the User1 schema.
  E. Grant the VIEW SERVER STATE permission to User1.
  B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
  C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

• Question 89:

  Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

  You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting

  database is only updated from the transactional database.

  The recovery model and backup schedule are configured as shown in the following table:

  

  One of the hard disk drives that stores the reporting database fails at 16:40 hours. You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal.

  What should you do?

  A. Perform a page restore.
  B. Perform a partial restore.
  C. Perform a point-in-time restore.
  D. Restore the latest full backup.
  E. Restore the latest full backup. Then, restore the latest differential backup.
  F. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  H. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  E. Restore the latest full backup. Then, restore the latest differential backup.