New Updated Microsoft MCSA 70-458 Real Exam Questions and Answers Download 11-20

Ensurepass

QUESTION 11

You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All

DBAs are members of the DOMAINJrDBAs Active Directory group. You grant DOMAINJrDBAs

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 DOMAINJrDBAs to the server role. You also need to provide the least level of

privileges necessary. Which SQL statement or statements should you use? Choose all that apply.

 

  1. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;

  2. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAINJrDBAs];

  3. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;

  4. GRANT VIEW DEFINITION TO [SpecialDBARole];

  5. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;

  6. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];

 

Correct Answer: BCF

 

 

QUESTION 12

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an

application. You need to evaluate existing and potential clustered and non-clustered indexes that

will improve performance. What should you do?

 

  1. Query the sys.dm_db_index_usage_stats DMV.

  2. Query the sys.dm_db_missing_index_details DMV.

  3. Use the Database Engine Tuning Advisor.

  4. Query the sys.dm_db_missing_index_columns DMV.

 

Correct Answer: C

 

 

QUESTION 13

DRAG DROP

You administer a Microsoft SQL Server 2012 instance that contains a database of confidential

data. You need to encrypt the database files at the page level. You also need to encrypt the

transaction log files. Which four actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

70-458-demo-7

 

Correct Answer:

70-458-demo-8

 

 

 

QUESTION 14

DRAG DROP

You administer a Microsoft SQL Server 2012 database. The database uses SQL Server Agent jobs

to perform regular FULL and LOG backups. The database uses the FULL recovery model. You plan

to perform a bulk import of a very large text file. You need to ensure that the following

requirements are met during the bulk operation:

 

  • The database transaction log is minimally affected.

  • The database is online and all user transactions are recoverable.

  • All transactions are fully recoverable prior to the import.

 

Which three actions should you perform in sequence?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

70-458-demo-9

 

Correct Answer:

70-458-demo-10

 

 

 

QUESTION 15

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size.

The Sales database is configured as shown in the following table.

 

70-458-demo-11

 

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum

amount of time. What should you do?

 

  1. Perform a file restore.

  2. Perform a transaction log restore.

  3. Perform a restore from a full backup.

  4. Perform a filegroup restore.

 

Correct Answer: A

 

 

QUESTION 16

HOTSPOT

You administer a Microsoft SQL Server 2012 database. The database contains a table that has the

following definition:

 

70-458-demo-12

 

You want to export data from the table to a flat file by using the SQL Server Import and Export

Wizard. You need to ensure that the following requirements are met:

 

  • The first row of the file contains the first row of data.

  • Each record is of the same length.

  • The date follows the U.S. date format.

  • The file supports international characters.

 

What should you do?

 

To answer, configure the appropriate option or options in the dialog box in the answer area.

 

Hot Area:

70-458-demo-13

70-458-demo-14

 

Correct Answer:

70-458-demo-15

 

 

 

QUESTION 17

DRAG DROP

You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the

following definition:

 

70-458-demo-16

 

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to

include only rows that contain a value in the CarrierTrackingNumber column. Which four

Transact- SQL statements should you use?

 

To answer, move the appropriate statements from the list of statements to the answer area and

arrange them in the correct order.

 

Select and Place:

70-458-demo-17

 

Correct Answer:

70-458-demo-18

 

 

QUESTION 18

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. 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 any of the tables in the Customers

schema. Which Transact-SQL statement should you use?

 

  1. REVOKE SELECT ON Schema::Customers FROM UserA

  2. DENY SELECT ON Object::Regions FROM UserA

  3. EXEC sp_addrolemember ‘Sales’, ‘UserA’

  4. DENY SELECT ON Object::Regions FROM Sales

  5. REVOKE SELECT ON Object::Regions FROM UserA

  6. DENY SELECT ON Schema::Customers FROM Sales

  7. DENY SELECT ON Schema::Customers FROM UserA

  8. EXEC sp_droproleraember ‘Sales’, ‘UserA’

  9. REVOKE SELECT ON Object::Regions FROM Sales

  10. REVOKE SELECT ON Schema::Customers FROM Sales

 

Correct Answer: F

 

 

QUESTION 19

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. The

Sales role is granted the Select permission on the Customers schema. You need to ensure that

UserA is disallowed to select from the Regions table. Which Transact-SQL statement should you

use?

 

  1. DENY SELECT ON Object::Regions FROM Sales

  2. DENY SELECT ON Schema::Customers FROM Sales

  3. REVOKE SELECT ON Object::Regions FROM Sales

  4. REVOKE SELECT ON Schema::Customers FROM Sales

  5. DENY SELECT ON Object::Regions FROM UserA

  6. DENY SELECT ON Schema::Customers FROM UserA

  7. REVOKE SELECT ON Object::Regions FROM UserA

  8. REVOKE SELECT ON Schema::Customers FROM UserA

  9. EXEC sp_addrolemerr.ber ‘Sales’, ‘DserA’

  10. EXEC sp droprolemember ‘Sales’, ‘UserA’

 

Correct Answer: E

 

 

QUESTION 20

You administer a SQL 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. The Sales role is

granted the Select permission on the Customers schema. You need to remove the Select

permission for UserA on the Regions table. You also need to ensure that UserA can still access all

the tables in the Customers schema, including the Regions table, through the Sales role

permissions. Which Transact-SQL statement should you use?

 

  1. DENY SELECT ON Object::Regions FROM UserA

  2. DENY SELECT ON Schema::Customers FROM UserA

  3. EXEC sp_addrolemember ‘Sales’, ‘UserA’

  4. REVOKE SELECT ON Object:: Regions FROM UserA

  5. REVOKE SELECT ON Object::Regions FROM Sales

  6. EXEC sp_droproiemember ‘Sales’, ‘DserA’

  7. REVOKE SELECT ON Schema::Customers FROM UserA

  8. DENY SELECT ON Object::Regions FROM Sales

  9. DENY SELECT ON Schema:: Customers FROM Sales

  10. REVOKE SELECT ON Schema:: Customers FROM Sales

 

Correct Answer: D

 

Instant Access to Download Latest Complete Collection of Microsoft MCSA 70-458 Real Exam

Try Microsoft MCSA 70-458 Free Demo