(Continued from previous part...)

You are developing security policy for your SQL Servers and have all of the data entry clerks needing access to a series of tables. You create a Data Entry role and assign the proper permissions as well as add the users.
You then find out that Bob is a part of the HR group because of cross training and needs to use the same objects from the same application except for the Vacation table, to which he should not have access. No column permissions are assigned. What should you do?

Answer
Create an new role and DENY permission to the Vacation table for this role. Add Bob to this role.
Explanation
To effectively handle security, you want to minimize the administrative burden. Bob is a member of the Data Entry role, and because of cross training, you do not want to remove him from this role, but you do need to DENY permission to the Vacation table. The best way to do this is with another role specifically to DENY this permission.

You are trying to track down issues with a SQL Server 2005 application using Profiler. The part of the application you are checking uses the EXECUTE AS statement to change the context of every user to the user Bob. How can you determine which user is executing statements in Profiler?

Answer
Check the Sessionloginname data column.
Explanation
The SESSIONLOGINNAME column, not shown by default, will contain the original login name of the user even if their context has changed in the application.

Assuming the column name is correct, which of the following is a valid cast operation in SSIS _expression language?

Answer
(DT_STR, 20, 1252)[MyColumn]
Explanation
The correct answer is (DT_STR, 20, 1252)[MyColumn]. The cast uses parens and then the cast type specification with the _expression to be converted afterwards.

There are two types of subscriptions in SQL Server replication. What are they?

Answer
Push/Pull
Explanation
The two types of subscriptions are push and pull.

(Continued on next part...)