Online Access Free 70-432 Exam Questions

You administer a SQL Server 2008 instance named SQL1. SQL1 contains a database named DB1.
You create a new user named User1 in the DB1 database. No additional permissions have been assigned to User1 or the public role. User1 will interact with database objects stored in a new schema named Reporting. The other objects in the DB1 database are owned by the dbo and are contained in a schema named Operations.
You need to grant the minimum necessary permission to User1 to create tables and stored procedures in the Reporting schema.
You also need to ensure that User1 cannot alter any of the objects in the Operations schema.
Which Transact-SQL statements should you execute?

• A.GRANT CREATE TABLE, CREATE PROCEDURE TO User1; DENY CONTROL ON SCHEMA: Operations TO User1;
• B.ALTER AUTHORIZATION ON SCHEMA::Reporting TO User1;
• C.GRANT CREATE TABLE, CREATE PROCEDURE ON SCHEMA: Reporting; TO User1;
• D.GRANT CONTROL ON SCHEMA::Reporting TO User1; DENY CONTROL ON SCHEMA::Operations TC User1;

You administer two SQL Server 2008 instances named Instance1 and Instance2.
A database named Customers resides on Instance1. You move the Customers database from Instance1 to Instance2.
A SQL Server login named User1 that has the password "111999" 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 Customers database on Instance2 by using the SQL Server login User1. However, the user receives an error message which indicates that the access to the Customers database is denied.
You need to ensure that User1 can access the Customers database.
Which Transact-SQL statements should you execute on Instance2?

• A.USE Customers; ALTER LOGIN User1 WITH PASSWORD = '111999' UNLOCK;
• B.USE Customers; ALTER LOGIN User1 WITH DEFAULT_DATABASE = Customers;
• C.USE Customers; ALTER USER User1 WITH LOGIN = User1;
• D.USE Customers; ALTER USER User1 ENABLE;

You administer a remote SQL Server 2008 instance that contains a database named InsightDB. The InsightDB database is used by an application that is continuously connected.
The application uses the INSERT command extensively and triggers the population of multiple tables.
Users of the application report that the application performance is poor. You suspect that the performance issues are related to blocking.
You need to monitor the state of the instance at regular intervals without affecting the application performance further.
Which tool should you use?

• A.SQL Server Profiler
• B.Windows System Monitor
• C.Dynamic Management Views
• D.SQL Server Resource Governor

You maintain a SQL Server 2008 instance that runs on a computer that hosts several applications. You configure the SQL Server Agent service to run by using the SERVER1\AGENT account. You create a job named MailingList that requires a file to be written to a file server. The job fails to run because it does not have appropriate access to the file server. You plan to configure the SQL Server Agent service.
You need to perform the configuration such that only the SQL Server Agent service has read and write access to the file server.
Which account type should you use?

• A.Local System account
• B.Domain account
• C.Local Service account
• D.Network Service account

You are mastering the company database. During the development, you find that Transact-SQL query below is running slowly:SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510' A clustered index exists on the VideoTitle column.
There is a nonclustered index on the ReleaseDate column which contains the UpcNum and RetailPrice columns. The result of the avg_fragmentation_in_percent is 30 percent, when you search the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table,In order to find this method to solve this problem, which is the correct answer?

• A.You should Re-create the index on only the ReleaseDate column.
• B.You should alter the query hint to force the optimizer to force a unclustered index seek.
• C.You should reproduce the whole indexes on the VideoTitle table.
• D.You should delete the query hint from the query.