Latest MCTS 70-432 Dumps Real Exam Download 111-120

Ensurepass

QUESTION 111

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the

administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.

Now you are checking and configuring the security of the instance.

According to the requirement of the security audit policy, only successful and failed logon attempts are

recorded in log files and if records cannot be written to the log files, the SQL Server instance is shut down.

You must configure the SQL Server instance making it not violate the security audit policy.

In the options below, which Transact-SQL statements should you run?

 

A. sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘default trace enabled’, 1; GO RECONFIGURE GO

B. sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘common criteria compliance enabled’, 1; GO RECONFIGURE GO

C. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH =’\MAIN_SERVERAudit’ ) WITH (ON_FAILURE = SHUTDOWN); GO CREATE SERVER AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON); GO ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON); GO

D. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH =’\MAIN_SERVERAudit’ ) WITH (ON_FAILURE = SHUTDOWN); GO CREATE DATABASE AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON); GO ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON); GO

 

Correct Answer: C

Explanation/Reference:

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (Database Engine). Beginning in SQL Server 2008 Enterprise, you can also set up automatic auditing by using SQL Server Audit. There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you must have to enable, store, and view audits on various server and database objects.

You can record server audit action groups per-instance, and either database audit action groups or database audit actions per database. The audit event will occur every time that the auditable action is encountered.

SQL Server Audit Components

An audit is the combination of several elements into a single package for a specific group of server actions

or database actions. The components of SQL Server Audit combine to produce an output that is called an

audit, just as a report definition combined with graphics and data elements produces a report.

SQL Server Audit uses Extended Events to help create an audit.

SQL Server Audit

The SQL Server Audit object collects a single instance of server or database-level actions and

groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple

audits per SQL Server instance.

When you define an audit, you specify the location for the output of the results. This is the audit

destination. The audit is created in a disabled state, and does not automatically audit any actions.

After the audit is enabled, the audit destination receives data from the audit.

Server Audit Specification

The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope. The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are the atomic events exposed by the Database Engine. These actions are sent to the audit, which records them in the target. Here only the answer C create Server audit for logins.

 

 

QUESTION 112

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the

administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a text file which is named SQL01.log on the E: drive. A SQL Server Agent job executes every 2

minutes and logs information to the text file.

You get a report from users saying that the sever doesn’t make response. As the technical support, you

check and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer

functions. You have to identify the reason why the SQL Server Agent service is unresponsive.

In the options below, which log should you check?

 

A. You should check SQL1.log

B. You should check SQLAGENT.OUT

C. You should check log_xx.trc

D. You should check ERRORLOG

 

Correct Answer: B

Explanation/Reference:

The file SQLAGENT.OUT stores the SQL Agent running log. All the information about the latest run of the Agent is there. You can also find information into the event viewer.

 

 

QUESTION 113

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the

administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.

DB1 has a table which is named Table01 and a stored procedure named Procedure01.

Procedure01 choose data from Table01 by using a sp_executesql Transact-SQL statement.

You company security rules forbid users to access tables directly in any database.

Look at the exception below:

“Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object ‘Table01’, database

‘DB1’, schema ‘dbo’.”

The exception is raised when Procedure01 is executed by users. You must make sure that e user can

successfully execute Procedure1 complying with the company rules.

So what action should you perform to achieve this goal?

 

A. You should execute the GRANT SELECT ON dbo.Table01 TO User1 Transact-SQL statement.

B. You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.

C. You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header

D. You should alter Procedure01 and add the EXECUTE AS USER = ‘dbo’ option immediately before the call to the sp_executesql stored procedure.

 

Correct Answer: C

Explanation/Reference:

EXECUTE AS OWNER specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used. Use EXECUTE AS OWNER in the following scenario: You want to be able to change owner of the module without having to modify the module itself. That is, OWNER automatically maps to the current owner of the module at run time. OWNER is the explicit owner of the module or, if there is not an explicit owner, the owner of the schema of the module at the time the module is executed. OWNER must be a singleton account and not a group or role. The ownership of the module cannot be changed to a group or role when the module specifies EXECUTE AS OWNER and has an explicit owner. The ownership of a schema cannot be changed to a role or group when it contains a module that specifies EXECUTE AS OWNER and the modules does not have an explicit owner. Functions (except inline table-valued functions), Stored Procedures, and DML Triggers { EXEC | EXECUTE } AS { CALLER | SELF | OWNER | ‘user_name’ } DDL Triggers with Database Scope { EXEC | EXECUTE } AS { CALLER | SELF | ‘user_name’ } DDL Triggers with Server Scope and logon triggers { EXEC | EXECUTE } AS { CALLER | SELF | ‘login_name’ } Queues { EXEC | EXECUTE } AS { SELF | OWNER | ‘user_name’ }

 

 

QUESTION 114

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the

administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.

DB1 is available to a Windows group which is named WIIKIGOSupervisors.

A Windows user named User1 is a member of the WIIKIGOSupervisors group. WIIKIGOSupervisors is a

member of the db_owner role in the DB1 database. You must make sure that the SQL Server instance is

(not?) available to User1.

In the DB1 database, which Transact-SQL statement(s) should you execute?

 

A. DROP LOGIN “WIIKIGOUser1”;

B. EXEC dbo.sp_droprolemember ‘db_owner’, ‘WIIKIGOUser1’;

C. EXEC dbo.sp_revokedbaccess ‘WIIKIGOUser1’; EXEC dbo.sp_revokelogin ‘WIIKIGOUser1’;

D. CREATE LOGIN “WIIKIGOUser1” FROM Windows;DENY CONNECT SQL TO “WIIKIGOUser1”;

 

Correct Answer: D

Explanation/Reference:

If I understand well, The group WIIKIGOSupervisors is dbo of DB1. WIIKIGOUser1 is part of the group and should only have deny on the database.. This user will inherit rights from the group. Or we don’t want him to connect to the instance, so we have to create one login for this user and specify one DENY CONNECT to this user.

 

 

QUESTION 115

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There are queries that have an estimated execution cost less than 3. You intend to configure the instance,

make it use a single thread for these queries.

In the options below, which sp_configure configuration option should you set?

 

A. You should set query governor cost limit

B. You should set cost threshold for parallelism

C. You should set priority boost

D. You should set precompute rank

 

Correct Answer: B

Explanation/Reference: Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors. Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

 

 

QUESTION 116

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

According to the company requirement, you intend to perform an installation of an instance of SQL Server

Reporting Services (SSRS) to the same machine.

Now your company CIO wants version histories of all deployed reports can be kept.

As a technical support, what should you do to ensure this?

 

A. You should use the http.sys listener to install the SSRS instance.

B. You should configure the SSRS database, make it use Native mode

C. You should configure the SSRS database, make it use SharePoint integrated mode

D. You should use the Internet Information Services default Web site to install the SSRS instance

 

Correct Answer: C

Explanation/Reference:

When created for SharePoint integrated mode, the report server database stores server properties, report execution snapshots, report history, subscription definitions, and schedules. It stores a secondary copy of reports, report models, shared data sources, and resources to improve processing performance on the server. Primary storage for report documents is in the SharePoint content databases.

 

 

QUESTION 117

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

In your company there is a server which contains a default SQL Server 2005 instance.

For a new application, you have to install a SQL Server 2008 instance on the server. Your company CIO

assigns a task to you.

You must make sure that the respective certified third-party applications have access to both database

instances. You have to achieve this goal without changing the existing application environments.

So what should you do to achieve this by using as little database administrative effort as possible?

 

A. You should install SQL Server 2008 as a named instance, and make the new application to use the new instance.

B. You should install SQL Server 2008 as the default instance, and make the new application to use the default instance.

C. You should upgrade the SQL Server 2005 application to use SQL Server 2008.

D. You should upgrade the SQL Server 2005 instance to a SQL Server 2008 instance.

 

Correct Answer: A

Explanation/Reference:

SQL Server supports multiple instances of the Database Engine, Analysis Services, and Reporting

Services on the same computer. You can also upgrade earlier versions of SQL Server, or install SQL

Server on a computer where earlier SQL Server versions are already installed.

If a default instance already exists on the computer, SQL Server must be installed as a named instance.

The following table shows side-by-side support for SQL Server 2008:

Existing instance of SQL Server 2008 Side-by-side support

SQL Server 2008 (32-bit) SQL Server 2000 (32-bit) SQL Server 2000 (64-bit) x64 SQL Server 2005 (32-bit) SQL Server 2005 (64-bit) x64 SQL Server 2008 (64-bit) IA64 SQL Server 2005 (64-bit) IA64 SQL Server 2008 (64-bit) x64 SQL Server 2000 (32-bit) SQL Server 2000 (64-bit) x64 SQL Server 2005 (32-bit) SQL Server 2005 (64-bit) x64

In this case, there is already one default instance SQL Server 2005, thus, we haveto install one named instance.

 

 

QUESTION 118

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

You intend to configure FILESTREAM data, enabling FILESTREAM for file I/O streaming access and

allowing remote client computers to have streaming access to FILESTREAM data.

You must make sure that FILESTREAM data is enabled.

In the options below, which service should you configure?

 

A. You should configure Distributed File System

B. You should configure SQL Server

C. You should configure SQL Server Full Text

D. You should configure SQL Server VSS Writer

 

Correct Answer: B

Explanation/Reference:

Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL

Server Database Engine.

This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.

To enable and change FILESTREAM settings

On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to

Configuration Tools, and then click SQL Server Configuration Manager.

In the list of services, right-click SQL Server Services, and then click Open.

In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you

want to enable FILESTREAM.

Right-click the instance, and then click Properties.

In the SQL Server Properties dialog box, click the FILESTREAM tab.

Select the Enable FILESTREAM for Transact-SQL access check box.

If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O

streaming access. Enter the name of the Windows share in the Windows Share Name box.

If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote

clients to have streaming access to FILESTREAM data.

Click Apply.

In SQL Server Management Studio, click New Query to display the Query Editor.

In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

 

 

QUESTION 119

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a single Database Mail profile. Reports are sent to all the company employees by using the Mail

profile. During office hours, a large volume of reports are sent by the sp_send_dbmail stored procedure.

Now you notice that it takes a long time for the reports to be sent to the company manager. As the

technical support, you have to speed up the delivery.

So what action should you perform to achieve this goal?

 

A. Another SMTP account should be added to the existing Database Mail profile

B. When you send the reports to the manager, you should use the @importance = high parameter.

C. You should change the Account Retry Attempts option of the Database Mail system properties

D. You should configure a new Database Mail profile. The new one will be used for sending the reports to the manager.

 

Correct Answer: D

Explanation/Reference:

A Database Mail profile is a collection of Database Mail accounts.

Profiles improve reliability in cases where an e-mail server becomes unreachable, by providing alternative

Database Mail accounts.

At least one Database Mail account is required.

 

 

QUESTION 120

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. You put forward a database maintenance plan. The

maintenance plan rebuilds indexes, checks database integrity, writes a report to a text file.

Now the company requires that when the maintenance plan executes abnormally, the operators can be

notified by an e-mail message.

You’ve been assigned this task. So you have to make modifications on the SQL Server instance.

What should you do?

 

A. For the SQL Server Agent, you should enable a fail-safe operator

B. You should modify the database maintenance plan, making it send an e-mail to the appropriate operators

C. You should modify the database maintenance plan, making it include a Notify Operator task that emails the appropriate operators.

D. You should modify the SQL Server Agent job. The job executes the database maintenance plan to notify the appropriate operators.

 

Correct Answer: D

Explanation/Reference:

In this case, you should add send job status to the operator via the following procedure:

This topic describes how to set notification options so Microsoft SQL Server Agent can send notifications

to operators about jobs.

To notify an operator of job status

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that

instance.

2. Expand SQL Server Agent, expand Jobs, right-click the job you want to edit, and select Properties.

3. In the Job Properties dialog box, select the Notifications page.

4. If you want to notify an operator by e-mail, check E-mail, select an operator from the list, and then

select one of the following:

When the job succeeds to notify the operator when the job completes successfully.

When the job fails to notify the operator when the job completes unsuccessfully.

When the job completes to notify the operator regardless of completion status.

5. If you want to notify an operator by pager, check Page, select an operator from the list, and then select

one of the following:

When the job succeeds to notify the operator when the job completes successfully.

When the job fails to notify the operator when the job completes unsuccessfully.

When the job completes to notify the operator regardless of completion status.

6. If you want to notify an operator by net send, check Net send, select an operator from the list, and then

select one of the following:

When the job succeeds to notify the operator when the job completes successfully.

When the job fails to notify the operator when the job completes unsuccessfully.

When the job completes to notify the operator regardless of completion status.

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.