(Continued from previous part...)

You want to disable the trigger dEmployee on the HumanResources.Employee table in the SQL Server 2005 AdventureWorks sample database. Which of the following statements will do the trick?

Answer
A and B

Explanation
Either of the commands in A and B will disable the trigger.

In SQL Server 2005, if you want the keys securing your data to expire over time, what type of key encryption would you choose?

Answer
Certificates

Explanation
Certificates have an expiration data associated with them. This allows you to issue them to users and force a reissue in order to maintain their access to data.

Which is larger in size in SQL Server 2005 if you are using symmetric keys?

Answer
Ciphertext

Explanation
Ciphertext is larger and can be calculated using the following formula:
Size = ( FLOOR (8 + D)/BLOCK) + 1) * (BLOCK + BLOCK + 16)
where D is the data size in bytes, BLOCK is the block size (8 or 16, depending on algorithm) and Size is the new size in bytes of the ciphertext.

What result would you expect from this statement?

Select * from dbo
.MyTable

Answer
All records from [dbo].[MyTable]

Explanation
The answer is all records from [dbo].[MyTable]. Qualified names can include white space before and after the dots. Restrictions apply only to the names themselves that may need quoted identifiers with embedded white space or invalid characters.

You are using SQL Server 2005 and you wish to find a number of details about stored procedures created. The details include date and time, the server that created the stored procedure, the login name and the contents. How would you do this? Sponsored by APress

Answer
Create a DDL trigger on CREATE_PROCEDURE database event

Explanation
The answer is Create a DDL trigger on CREATE_PROCEDURE database event You can find the answer on page 448 of Beginning SQL Server 2005 for Developers: From Novice to Professional

A .NET assembly running with UNSAFE permissions can do which of the following?

Answer
All of the above

Explanation
CLR code running with UNSAFE permissions can access virtually any resource inside or outside of SQL Server without restriction. This is one reason only a sysadmin can create an assembly as UNSAFE.

What does the SQL Writer service do?

Answer
Handles Volume Shadow Service Copy functions.

Explanation
The SQL Writer service was introduced to handle access to the data files in SQL Server. It allows backup programs, like VSS, to function while SQL Server is still running.

Which key provides the strongest encryption?

Answer
AES (256 bit)

Explanation
The longer the key, the better the encryption, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.

You are delegating permissions on your SQL Server 2005 server to other adminstrators. You have local, single server jobs on one server that you would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign?

Answer
SQLAgentUserRole

Explanation
SQL Server 2005 provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allows the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.

(Continued on next part...)