Online Access Free 70-432 Exam Questions

You administer a SQL Server 2008 instance for a company named Contoso Ltd. The instance contains a database named DB1.
A Windows group named CONTOSO\Managers can access the DB1 database. CONTOSO\Managers is a member of the db_owner role in the DB1 database.
A Windows user named User1 is a member of the CONTOSO\Managers 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.EXEC dbo.sp_droprolemember ' db_owner ', 'CONTOSO\User1';
• B.EXEC dbo.sp_revokedbaccess 'CONTOSO\User1'; EXEC dbo.sp_revokelogin 'CONTOSO\User1';
• C.CREATE LOGIN "CONTOSO\User1" FROM Windows; DENY CONNECT SQL TO "CONTOSO\User1";
• D.DROP LOGIN "CONTOSO\User1";

You administer a SQL Server 2008 instance. A payroll application is used to store sensitive data in a database named PayrollDB.
You plan to implement the following security measure:
- All the data files, log files, and backup files of the database are automatically encrypted.
You need to devise a method to encrypt data. What should you include in your solution?

• A.Use a certificate to protect the data encryption key. Export the certificate to a file.
• B.Use cell-level encryption to protect sensitive data.
• C.Use a symmetric key provisioned through Extensible Key Management (EKM).
• D.Use Transparent Data Encryption (TDE) for the PayrollDB database.

You maintain a SQL Server 2008 instance that contains a database named Finance. In your absence, a user with a login named Rob will maintain the database snapshots. You need to giant the appropriate permissions to the user to delete the database
snapshots for the Finance database. Which database permission should you giant?

• A.DROP DATABASE
• B.ALTER ANY DATASPACE
• C.CONTROL
• D.DELETE

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.CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers', 'User1';
• B.CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers'. 'User1';
• C.GRANT VIEW ANY DEFINITION TO User1;EXEC sp_addrolemember 'db_datareader', 'User1';
• D.CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader'. 'Developers';EXEC sp_addrolemember 'sp_dbddladmin\ 'Developers';EXEC sp_addrolemember 'Developers', 'User1';

You administer a Microsoft SQL Server 2008 R2 database instance.
You need to identify queries or stored procedures that are consuming server resources.
You also need to view the query plan that is being used by each of these queries or procedures.
Which Dynamic Management Views (DMVs) should you use to find details about specific queries? (Choose all that apply.)

• A.dm_exec_query_plan
• B.dm_exec_connections
• C.dm_exec_query_stats
• D.dm_exec_sql_text
• E.dm_io_virtual_file_stats