Microsoft 70-432 Online Practice Questions and Exam Preparation

Microsoft 70-432 Online Questions & Answers

• Question 81:

  You administer a SQL Server 2008 instance. A database developer named User1 views the definitions of all database objects in a database to read data from all user-defined tables, views, and table-valued functions. You need to set the required permissions for User1. You also need to ensure that the same permissions can be granted to other developers by executing minimum number of Transact-SQL statements. Which Transact- SQL statements should you execute?

  A. GRANT VIEW ANY DEFINITION TO User1;EXEC sp_addrolemember 'db_datareader', 'User1';
  B. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers', 'User1';
  C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers'. 'User1';
  D. CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader'. 'Developers';EXEC sp_addrolemember 'sp_dbddladmin\ 'Developers';EXEC sp_addrolemember 'Developers', 'User1';
  C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers'. 'User1';

• Question 82:

  You maintain an instance of Microsoft SQL Server 2008. The instance contains a database named Finance. The recovery model of the Finance database is set to Full.

  You deploy a new process that modifies 10.000 records from the Accounts table at 19:00 hours daily.

  You need to ensure that any modification to the data can be reverted without the database going offline.

  Which strategy should you implement?

  A. Database snapshots
  B. Differential backup
  C. Transaction log backup
  D. Primary filegroup backup
  A. Database snapshots

• Question 83:

  You administer a Microsoft SQL Server 2008 R2 database that contains a schema named Reporting. The Reporting schema contains store procedures, tables, and views.

  Stored procedures and views in the Reporting schema access objects that are not contained in the schema.

  You need to ensure that the DOMAIN\BusinessReporting Active Directory group meets the following requirements:

  What should you grant the DOMAIN\BusinessReporting group?

  A. Access to the db_datareader role in the database
  B. The EXECUTE permissions on the Reporting schema
  C. The EXECUTE permissions to each schema that is queried by the stored procedures and views in the Reporting schema
  D. Access to the sysadmin server role
  D. Access to the sysadmin server role

• Question 84:

  You administer two SQL Server 2008 instances named Instance 1 and Instance 2. A database named AdventureWorks resides on Instance1. You move the AdventureWorks database from Instance1 to Instance2.

  A SQL Server login named Mary with the password "Re#99$45" is used by a user to access the database on Instance1. You create the same SQL Server login on Instance2. The user attempts to access the AdventureWorks database on

  Instance2 by using the SQL Server login Mary. However, the user receives an error message which indicates that the access to the AdventureWorks database is denied.

  You need to ensure that the user can access the AdventureWorks database.

  Which Transact-SQL statements should you execute on Instance!?

  A. USE AdventureWorks; ALTER USER Mary WITH LOGIN = Mary;
  B. USE AdventureWorks: ALTER LOGIN Mary ENABLE;
  C. USE Adventure Works; ALTER LOGIN Mary WITH PASSWORD = 'Re#99$45' UNLOCK;
  D. USE AdventureWorks; ALTER LOGIN Mary WITH DEFAULT_DATABASE = AdventureWorks;
  A. USE AdventureWorks; ALTER USER Mary WITH LOGIN = Mary;

• Question 85:

  You are mastering the company database; a new SQL Server 2008 computer named TesterDB should be set by yourself. Reporting Services will work in TesterDB. It should be set to run database backups and other maintenance job automatically. In the network environment, the company has no other SQL Server computers. The whole approach SQL2 will be made by using SQL1s DNS name. You should enable any necessary devices on SQL2. Which is the correct answer?(select more than one)

  A. You should utilize SQL Server Web Service technology
  B. You should utilize SQL Server Browser technology
  C. You should utilize Internet Information Services technology
  D. You should utilize Microsoft Distributed Transaction Coordinator technology
  B. You should utilize SQL Server Browser technology
  D. You should utilize Microsoft Distributed Transaction Coordinator technology

• Question 86:

  Your workstation has installed Microsoft SQL Server Management Studio (SSMS), and not installed Microsoft Business Intelligence Development Studio (BIDS).

  You should design a package, which has the following features:

  The package should be transactional.

  The package should be optimized for 10 tables.

  The package should be stored safely in the msdb database of a server which is remote.

  Which is correct?

  A. You should create the package by using DTS Designer.
  B. You should create the package by using the Package Migration Wizard.
  C. You should create the package by using the Microsoft SQL Server Import and Export Wizard.
  D. On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, You should click the Create Package submenu.
  C. You should create the package by using the Microsoft SQL Server Import and Export Wizard.

• Question 87:

  You administer a SQL Server 2008 instance that contains a database named DB1.

  A table named Sales.Table1 exists in the Sales schema.

  You need to move the Sales.Table1 table to a new schema named Billing.

  Which Transact-SQL statement should you execute?

  A. ALTER SCHEMA Billing TRANSFER Sales.Table1;
  B. ALTER USER Sales WITH DEFAULT_SCHEMA = Billing;
  C. ALTER AUTHORIZATION ON Sales.Table1 TO Billing:
  D. ALTER TABLE Sales.Table1 SWITCH TO Billing.Table1;
  A. ALTER SCHEMA Billing TRANSFER Sales.Table1;

• Question 88:

  You administer a SQL Server 2008 instance. You plan to install a SQL Server Analysis Services (SSAS) instance. The SSAS instance will be accessed by an Internet Information Services application by using anonymous access. You need to ensure that the application can access the SSAS instance. What should you do?

  A. Set the Security\RequireClientAuthentication server configuration to True.
  B. Set the Security\RequireClientAuthentication server configuration to False.
  C. Add the Kerberos Security Support Provider Interface (SSPI) provider to the Security\SecurityPackageList server configuration.
  D. Add the NTLM Security Support Provider Interface (SSPI) provider to the Security\SecurityPackageList server configuration.
  B. Set the Security\RequireClientAuthentication server configuration to False.

• Question 89:

  You maintain a SQL Server 2008 instance that contains a database named AdventureWorks.

  The AdventureWorks database contains a table named OrderSpecs. The OrderSpecs table has a clustered primary key named OrderId on the OrderId column. The OrderSpecs table has a single XML column named ProductSpecs that

  stores XML data, and there is an XML index on the same XML column.

  You add a new column named ProductId to the OrderSpecs table.

  You need to include ProductId in the primary key.

  What should you do?

  A. Alter the XML index and set the ALLOW_ROW_LOCKS = OFF option. Alter the primary key and set the ALLOW_ROW_LOCKS = ON option.
  B. Drop the XML index on the table. Modify the primary key. Recreate the XML index.
  C. Disable the XML index on the ProductSpecs column. Modify the primary key. Enable the XML index on the ProductSpecs column.
  D. Move the XML data to a temporary table. Clear the XML data from the original table by setting the ProductSpecs column to NULL. Modify the primary key. Repopulate the ProductSpecs column.
  B. Drop the XML index on the table. Modify the primary key. Recreate the XML index.

• Question 90:

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

  The DB1 database includes a stored procedure named Procedure1. Procedure 1 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 EXECUTE 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 stored procedure.
  C. Alter Procedure1 and add the WITH EXECUTE AS OWNER option to its header.