Latest MCTS 70-432 Dumps Real Exam Download 61-70

Ensurepass

QUESTION 61

You administer a SQL Server 2008 instance named DB1. DB1 contains a table named Table1.

The DB1 database includes a stored procedure named Procedure1. Procedure1 uses a sp_executesql

Transact-SQL statement to select data from Table1.

According to business requirements, users are not allowed to access tables directly in any database.

When a user executes Procedure1, the following exception is raised:

“Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object ‘Table1′, database “DB1’, schema ‘dbo’.”

You need to ensure that the user can successfully execute Procedure1 without violating the business requirements.

What should you do?

 

A.            Execute the GRANT SELECT ON dbo.Table1 TO User1 Transact-SQL statement.

B.            Execute the GRANT SELECT ON dbo.Procedure1 TO User1 Transact-SQL statement.

C.            Alter Procedure1 and add the WITH EXECUTE AS OWNER option to its header.

D.            Alter Procedure1 and add the EXECUTE AS USER = ‘dbo’ option immediately before the call to the sp_executesql procedure.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 62

You administer a SQL Server 2008 instance. You use an internal application based on SQL Server 2008.

The application uses Analysis Services and Reporting Services.

Company security requires that the surface area for all the deployed components of SQL Server 2008 be configured.

You need to implement the security policy and devise a method to evaluate the security policy against other database servers.

What should you do?

 

A. Create policies based on the appropriate facets. Apply the policies against a server group that includes the database servers that need to be configured.

B.            Create a Transact-SQL script based on the sp_configure stored procedure. Use a configuration server to run the script against a server group that includes the database servers that need to be configured.

C. Use the SQL Server Best Practices Analyzer (BPA) to analyze your database servers. Implement the recommendations of the BPA.

D. Edit the RSReportServer.config configuration file. Distribute the file to all database servers that need to be configured.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 63

You administer a SQL Server 2008 instance that runs a Windows Server 2003 computer. That instance uses mixed authentication mode. You need to ensure that the SQL Server 2008 authentication logins follow the same password complexity rules that are enforced by Windows Server 2003. You also need to ensure that the password complexity rules continue to be enforced.

Which two actions should you perform? (Each correct answer presents part of the solution. Choose TWO.)

 

A. Create a SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.

B. Use Policy-Based Management to create a policy that prevents any violation of these rules.

C. Use the ALTER LOGIN …CHECK_EXPIRATION = ON statement to modify all logins.

D. Use the ALTER LOGIN … HASHED statement to modify all logins.

E. Use the ALTER LOGIN …CHECK_POLICY = ON statement to modify all logins.

 

Correct Answer: BE

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.

Next, create one policy that prevents subsequent logins from being created without the option

CHECK_POLICY = ON.

 

 

QUESTION 64

You maintain a SQL Server 2008 instance that contains a database named CustomerDB. The CustomerDB database stores customer data for the company. The customers use a Web application to access their profile data. You need to protect the customer data such that data files, log files, and subsequent backups are as secure as possible even if the backup media is lost. You want to achieve this goal without affecting the Web application. What should you do?

 

A. Make the CustomerDB database accessible only through stored procedures and functions.

B. Enable Transparent Database Encryption for both the CustomerDB database and the master database.

C. Enable Transparent Database Encryption for the CustomerDB database and back up the transaction log.

D. Encrypt the sensitive data at the cell level by using the built-in encryption functions.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 65

You administer a SQL Server 2008 instance for a company named Contoso Ltd. The instance contains a database named DB1.

A Windows group named CONTOSOManagers can access the DB1 database. CONTOSOManagers is a member of the db_owner role in the DB1 database.

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

You need to ensure that User1 is unable to access the SQL Server instance.

Which Transact-SQL statement(s) should you execute in the DB1 database?

 

A. CREATE LOGIN “CONTOSOUser1” FROM Windows; DENY CONNECT SQL TO “CONTOSOUser1”;

B. DROP LOGIN “CONTOSOUser1”;

C. EXEC dbo.sp_droprolemember ‘db_owner’, ‘CONTOSOUser1’;

D. EXEC dbo.sp_revokedbaccess ‘CONTOSOUser1’; EXEC dbo.sp_revokelogin ‘CONTOSOUser1’;

 

Correct Answer: A

Explanation/Reference:http://msdn.microsoft.com/en-us/library/ms182763.aspxDenying CONNECT SQL permission to a SQL Server login and principals to which the login hasregranted it

The following example denies CONNECT SQL permission to the SQL Server login Annika and to the principals to which she has granted the permission.

USE master;

DENY CONNECT SQL TO Annika CASCADE;

GO

DROP LOGIN: Removes a SQL Server login account that pre-exists.

sp_denylogin (NOT CORRECT): Removes the login entries from SQL Server for a Windows user or group created by using CREATE LOGIN, sp_grantlogin, or sp_denylogin. HOWEVER — Per MSDN:

“This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

 

 

QUESTION 66

You administer a SQL Server 2008 instance that contains a very large database named FinanceDB.

You plan to create a maintenance plan that meets the following objectives for the FinanceDB database:

It executes the DBCC CHECKDB statement.

It rebuilds all the indexes.

It updates all the index statistics.

You need to ensure that the maintenance plan is executed in the minimum amount of time.

Which two actions should you perform? (Each correct answer presents part of the solution. Choose TWO.)

 

A. Use the Rebuild Index task.

B. Use the Update Statistics task.

C. Use the Shrink Database task.

D. Use the Reorganize Index task.

E. Use the Check Database Integrity task.

 

Correct Answer: AE

Explanation/Reference:

 

 

QUESTION 67

You manage a SQL Server 2008 instance.

You upgrade a SQL Server 7.0 database to the SQL Server instance.

You need to ensure the early detection of suspect pages in the database.

What should you do?

 

A.            Set the PAGE_VERIFY database option to CHECKSUM for the database.

B.            Set the PAGE_VERIFY database option to TORN_PAGE_DETECTION for the model database.

C. Set the database compatibility level option to 10 for the database.

D.            Turn on the TRUSTWORTHY database option for the database.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 68

You maintain a SQL Server instance.

You use the Resource Governor to manage processor resources on the server for a newly deployed application named NewApp. NewApp is assigned to a custom workload group named OurApp.

You need to monitor processor resources.

What should you do?

 

A.            Use the sys.dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer: Workload Group Stats performance object for the default workload group.

B.            Use the request_max_cpu_time_sec column of the sys.resource_governor_workload_groupscatalog view.

C.            Use the sys.dm_resource_workload_groups dynamic management view to monitor the CPU usage for the default workload group.

D.            Use the sys.dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer: Workload Group Stats performance object for the OurApp workload group.

 

Correct Answer: D

Explanation/Reference:

 

 

QUESTION 69

You are the administrator of a SQL Server 2008 instance named Instance01.

Instance01 contains a database which is named SellingHelp. There isthe Products table in the SellingHelp database.

Now according to the company requirement, the Products table has to be replicated to the SQL Server instances installed in laptops computers. You have to create a Replication topology to perform this.

The Products table is updated from Instance01 when the laptops reconnect to the corporate network. The

Products table on Instance01 is frequently updated between reconnections.

You have to make sure that you can implement the Replication topology along with the Subscription type successfully and meanwhile reduce the bandwidth usage to the least.

Which action should you perform to achieve this goal?

 

A. You should implement the Merge Replication topology along with a Pull Subscription.

B. You should implement the Snapshot Replication topology along with a Push Subscription.

C. You should implement the Transactional Replication topology along with a Pull Subscription.

D. You should implement the Snapshot Replication topology along with a Pull Subscription.

 

Correct Answer: A

Explanation/Reference:

Merge replication is typically used in server-to-client environments. Like transactional replication, typically, a merge replication starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

 

 

QUESTION 70

You are the administrator of a SQL Server 2008 instance.

You make sure that all SQL Server instances are consistently configured for naming conventions, security settings, force index creation and avoidance of data fragmentation.

Which action should you perform to achieve this goal?

 

A. You should use the Database Engine Tuning Advisor.

B. In Microsoft SQL Server Management Studio, you should create a maintenance plan.

C. You should use the SQL Server Configuration Manager.

D. In Microsoft SQL Server Management Studio, you should create a policy.

 

Correct Answer: D

Explanation/Reference:

Policy-Based Management lets you monitor best practices for the SQL Server Database Engine.

 

 

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