Latest MCTS 70-432 Dumps Real Exam Download 151-160

Ensurepass

QUESTION 151

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.

The company wants to export data from SQL Server to a Microsoft Excel file. The company assigns this

task to you. You perform this by using the SQL Server Import and Export Wizard.

After this, you save the package definition to a file. Now you have to reexecute the package from the

command line.

In the options below, which utility should you choose to use?

 

A. bcp.exe

B. dta.exe

C. dtexec.exe

D. sqlmaint.exe

 

Correct Answer: C

Explanation/Reference: The dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.

Note: On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or Business Intelligence Development Studio during setup.

Note: When you use the version of the dtexec utility that comes with SQL Server 2008 to run a SQL Server 2005 Integration Services (SSIS) package, Integration Services temporarily upgrades the package to SQL Server 2008 Integration Services (SSIS). However, you cannot use the dtexec utility to save these upgraded changes. For more information about how to permanently upgrade a SQL Server 2005 package to SQL Server 2008, see Upgrading Integration Services Packages.

 

 

QUESTION 152

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 named Dworks in the instance. The Dworks table has a table named

BillDetails which has a clustered primary key named BillId on the BillId column.

The BillDetails table has a single XML column named GoodsDetails. The XML column has an XML index

and XML data are stored in this XML column.

A new column named GoodsID is added to the BillDetails table. GoodsID must be included in the primary

key.

So what action should you perform to achieve this goal?

 

A. You should drop the XML index on the table. Modify the primary key. Recreate the XML index

B. You should alter the XML index and set the ALLOW_ROW_LOCKS = OFF option. Alter the primary key and set the ALLOW_ROW_LOCKS = ON option.

C. You should move the XML data to a temporary table. Clear the XML data from the original table by setting the GoodsDetails column to NULL. Modify the primary key. Repopulate the ProductSpecs column.

D. You should disable the XML index on the GoodsDetails column. Modify the primary key.Enable the XML index on the ProductSpecs column.

 

Correct Answer: A

Explanation/Reference:

1.

 In order to change a primary key, you have to drop it, or, A PRIMARY KEY constraint cannot be deleted

if the following exist:

If it is referenced by a FOREIGN KEY constraint in another table; the FOREIGN KEY constraint must

be deleted first.

The table has a PRIMARY XML index applied on it.

2.

 XML indexes fall into the following categories:

Primary XML index

Secondary XML index

The first index on the xml type column must be the primary XML index. Using the primary XML index,

the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY. Depending on

the type of queries, these secondary indexes might help improve query performance.

Thus, you have first to drop the XML index as it is the primary index and this forbit the primary key to be

dropped.

 

 

QUESTION 153

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 n On-Line Analytical Processing (OLAP) database named in the instance. The database

contains a dimension table named Clients. Ever hour backup of data of the Clients table is performed. But

the Clinets table contains redundant data. You must keep the disk space used to store the Clients table.

In the options below, which compression technology should you use?

 

A. You should use row compression

B. You should use page compression

C. You should use backup compression

D. You should use windows NTFS file system compression

 

Correct Answer: B

Explanation/Reference:Page Compression The second and the most vital compression method is page compression. Page compressionallows common data to be shared between rows for a given page. Its uses the following techniques to compress data: Row compression. (Already discussed above) Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used. Dictionary Compression. Dictionary compression searches for duplicate values through out the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page. Because of the Prefix Compression and Dictionary Compression, duplicate values are shared between rows in a given page. Which lead us to reduce the disk space used.

 

 

QUESTION 154

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. According to the requirements of the security audit policy,

only successful and failed logon attempts are recorded in log files; if records cannot be written to the log

files, the SQL Server instance is shut down.

You have to make the SQL Server instance comply with the security audit policy by performing the

configuration.

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

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

 

Correct Answer: D

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 155

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 which uses mixed authentication mode. The

instance runs on a computer which has Windows Server 2003 installed. You must make sure that the SQL

Server 2008 authenticated logins follow the same password complexity rules that are enforced by

Windows Server 2003.

Besides this, you must make sure that the password complexity rules continue to be enforced.

So what action should you perform to achieve this goal? (choose more than one)

 

A. You should create an SQL Server Agent job. If a rule violation is detected, the job runs periodically to raise an alert

B. You should modify all logins by using the ALTER LOGIN … HASHED statement.

C. You should modify all logins by using the ALTER LOGIN … CHECK_POLICY = ON statement to.

D. You should modify all logins by using the ALTER LOGIN … CHECK_EXPIRATION = ON statement.

E. You should create a policy by using Policy-Based Management. The policy prevents any violation of these rules

 

Correct Answer: CE

Explanation/Reference:

First, the alter login will change all the created logins to use the policy CHECK_POLICY = { ON | OFF } Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON. Create one policy that prevent the next logins to be created without the option CHECK_POLICY = ON

 

 

QUESTION 156

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.

You have set the recovery model of DB1 to Full. The table below shows the backup strategy for DB1.

Your company stores the system databases, user database data, and log files on separate disks. A user

report that the hard disks which store the user database data files have failed at 15:00 on Wednesday.

According to the requirement of the company CIO, you have to restore DB1 from the database backups,

and you must reduce the data loss as soon as possible.

So what action should you perform to achieve this goal?

 

A. First you should restore the full backup taken on Sunday. Then you restore the differential backup taken on Wednesday.

B. First you should back up the transaction log. Then restore the full backup taken on Sunday. At last restore all transaction log backups

C. First you should back up the transaction log. Second, restore the full backup taken on Sunday. Third, restore the differential backup taken on Tuesday and restore all transaction log backups taken during Wednesday.

D. First you should restore the full backup taken on Sunday. Then restore the differential backup taken on Monday and Tuesday and restore the transaction log backups taken at 08:00 hours and at 12:00 hours on Wednesday.

 

Correct Answer: C

Explanation/Reference:

1.

 As the transaction log is on a separate disk and this disk is still healthy, you can take a backup of the transaction log. This backup will contain all the commited transactions that have been applied to the database since the latest transaction log backup at 12:00.

2.

 Restore the full backup of Sunday with NORECOVERY

3.

 Restore the differential backup of Tuesday with NORECOVERY

4.

 restore all the transactions log backup with NORECOVERY except for the latest you took. For this backup you will use the RECOVERY option.

 

 

QUESTION 157

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.

Now according to the requirement of the company CIO, users on the SQL Server instance must be able to

use the OPENROWSET() function to query remote data source.

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

 

A. Agent XPs

B. remote access

C. remote proc trans

D. Ad Hoc Distributed Queries

 

Correct Answer: D

Explanation/Reference:

By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access. Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server.

 

 

QUESTION 158

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. The DB1 database contains the following stored

procedure. (Line numbers are useded for reference only.)

01 CREATE PROCEDURE Sales.Procedure1

02 AS

03 IF OBJECT_ID(‘Service.Table’) IS NOT NULL

04 DROP TABLE Service.Table;

06 CREATE TABLE Service.Table (

07 Id int PRIMARY KEY CLUSTERED,

08 Name varchar(100);

09 );

11 …

12 GO

The following exception is raised when a user tries to invoke Procedure1, “Msg 262, Level 14, State 1,

Procedure Procedure1, Line 5 CREATE TABLE permission denied in database ‘DB1’.”

You should grant the user access to execute Procedure1, you must assign only the required permissions.

What action should you do perform?

 

A.

B.

C.

D.

 

A. Between lines 01 and 02, you should insert the WITH EXECUTE AS ‘dbo’ clause.

B. Between lines 01 and 02, you should insert the EXECUTE AS USER = ‘dbo’ statement.

C. You should give the user the ALTER permission on the Service schema

D. You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.

 

Correct Answer: A

Explanation/Reference:

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

[ { @parameter [ type_schema_name. ] data_type }

[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]

] [ ,…n ]

[ WITH <procedure_option> [ ,…n ] ]

[ FOR REPLICATION ]

AS { <sql_statement> [;][ …n ] | <method_specifier> }

[;]

<procedure_option> ::=

[ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]<sql_statement> ::=

{ [ BEGIN ] statements [ END ] }

<method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name EXECUTE AS Specifies the security context under which to execute the stored procedure. In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers. By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between userdefined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module. EXECUTE } AS { CALLER | SELF | ‘user_name’ ‘ user_name ‘ Specifies the statements inside the module execute in the context of the user specified in user_name. Permissions for any objects within the module are verified against user_name. user_name cannot be specified for DDL triggers with server scope or logon triggers. Use login_name instead. user_name must exist in the current database and must be a singleton account. user_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITYLocalService, NT AUTHORITY NetworkService, or NT AUTHORITYLocalSystem.

 

 

QUESTION 159

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. In the Services schema, There is a table named Service.

Table. The Services.Table has to be moved from the Sales schema to a new schema named Costs.

In the options below, which Transact-SQL statement should you execute?

 

A. ALTER TABLE Service.Table SWITCH TO Costs.Table1;

B. ALTER SCHEMA CostsTRANSFER Service.Table;

C. ALTER USER Service WITH DEFAULT_SCHEMA = Costs;

D. ALTER AUTHORIZATION ON Service.Table1 TO Costs;

 

Correct Answer: B

Explanation/Reference:

ALTER SCHEMA : Transfers a securable between schemas. ALTER SCHEMA schema_name TRANSFER securable_name schema_name Is the name of a schema in the current database, into which the securable will be moved. Cannot be SYS or INFORMATION_SCHEMA. securable_name Is the one-part or two-part name of a schema-contained securable to be moved into the schema. ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.

 

 

QUESTION 160

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

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

The two instances are respectively named InstanceA and InstanceB. There is a database named Dworks

on InstanceA. You move the Dworks database to InstanceB. A user use a SQL Server login named Mary

to access the database on InstanceA with the password “Tk@87#34”. On InstanceB, you create the same

SQL Server login.

The user tries to use the SQL Server login Mark to access the Dworks database on InstanceB. But the

user gets an error message, according to the indication of the message, the access to the Dworks

database has been denied.

You must make sure that the user can access the Dworks database on InstanceB successfully.

So on InstanceB, which Transact-SQL statements should you execute?

 

A. USE AdventureWorks;ALTER USER Mary WITH LOGIN = Mary;

B. USE AdventureWorks;ALTER LOGIN Mary ENABLE;

C. USE AdventureWorks;ALTER LOGIN Mary WITH PASSWORD = “Tk@87#34” UNLOCK;

D. USE AdventureWorks;ALTER LOGIN Mary WITH DEFAULT_DATABASE = AdventureWorks;

 

Correct Answer: A

Explanation/Reference:

ALTER USER : Renames a database user or changes its default schema.

ALTER USER userName

WITH <set_item> [ ,…n ]

<set_item> ::=

NAME = newUserName

| DEFAULT_SCHEMA = schemaName

| LOGIN = loginName

Arguments

userName

Specifies the name by which the user is identified inside this database.

LOGIN = loginName

Re-maps a user to another login by changing the user’s Security Identifier (SID) to match the login’s SID.

NAME = newUserName

Specifies the new name for this user. newUserName must not already occur in the current database.

DEFAULT_SCHEMA = schemaName

Specifies the first schema that will be searched by the server when it resolves the names of objects for this

user.

Renames a database user or changes its default schema.

Remarks

If DEFAULT_SCHEMA is left undefined, the user will have dbo as its default schema.

DEFAULT_SCHEMA can be set to a schema that does not currently occur in the database. Therefore, you

can assign a DEFAULT_SCHEMA to a user before that schema is created. DEFAULT_SCHEMA cannot

be specified for a user who is mapped to a Windows group, a certificate, or an asymmetric key.

Important:

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role.

All members of the sysadmin fixed server role have a default schema of dbo.

You can change the name of a user who is mapped to a Windows login or group only when the SID of the

new user name matches the SID that is recorded in the database. This check helps prevent spoofing of

Windows logins in the database.

The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users

mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause.

Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be

used to change the type of user, such as changing a Windows account to a SQL Server login.

The name of the user will be automatically renamed to the login name if the following conditions are true.

The user is a Windows user.

The name is a Windows name (contains a backslash).

No new name was specified.

The current name differs from the login name.

Otherwise, the user will not be renamed unless the caller additionally invokes the NAME clause.

 

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