Exam Prep Questions

1:	You are a senior developer working at a consulting firm. You have to make sure that a user does not enter an alphabetical character in the Telephone, Date, and SSN columns of the Client table. What is the best way to implement this type of validation? A. Create an INSERT TRigger. B. Create an INSTEAD OF TRigger. C. Use CHECK constraints. D. There is no way to implement this.
A1:	Answer: C. Making sure that a user does not enter data of a different type is not best done with a trigger because the same can be done with a CHECK constraint, which processes faster and with less overhead than a trigger. For more information about different ways to constrain data input, see the "Constraints to Control Input" section of this chapter.
2:	Harold has created an Employee table. The table contains confidential information, so he has granted SELECT permissions on the table only to the other developers. There is an HR role that contains all the employees in the personnel department. You create a view and grant SELECT permissions on the view to the HR role. When members of the HR role attempt to retrieve data from the view, they receive the following error message: SELECT permission denied on object 'Employees', database 'Northwind', owner 'Harold' You as one of the developers must ensure that the HR role can use the view only to access the data. What should you do? A. Add the HR role to the sysadmin fixed server role. B. Transfer the ownership of the view to the HR role. C. Instruct Harold to transfer the ownership of the table to the HR role. D. Instruct Harold to grant the users SELECT permissions on the table. E. Drop the view. Instruct Harold to re-create the view and to grant SELECT permissions on the view to the HR role.
A2:	Answer: E. Remember that in a proper system all the objects should belong to the same person to prevent a broken ownership chain, which could cause permission and performance problems. Never provide an individual with more power than the person needs, and in particular limit the members of the sysadmin role to a very few trusted and competent individuals.
3:	You have an Employees table in the company database. A clerk requires access to the entire table. All clerks have been denied permissions on the employee salaries and pay codes but have been granted SELECT permissions on all the other data. What do you do to give the clerk access? A. Revoke SELECT permissions on the salaries and pay codes for the clerk. B. Add the clerk to the db_datareader database role. C. Add the clerk to the db_accessadmin database role. D. Grant SELECT permissions on the salaries and pay codes for the clerk.
A3:	Answer: D. The only thing that is necessary in this scenario is to change the permissions on the individual columns for the clerk. If you use the clerk's own account, you should not affect any of the other users. Assigning the user to any of the roles would not change the fact that the clerk has been denied permission to access some of the data.
4:	You want to prevent a user from changing data but still allow him to create objects for other users. The user currently is a user in the database with no other permissions. No other permissions have yet to be set. What should you do? (Select two answers.) A. Deny SELECT, UPDATE, and DELETE permissions to the user. B. Deny SELECT permissions to the user. C. Deny UPDATE and DELETE permissions to the user. D. Add the user to the db_owner database role. E. Add the user to the db_ddladmin database role. F. Grant the user CREATE VIEW permissions.
A4:	Answer: C and E. Because the user is already in the database, you will have to deny UPDATE and DELETE permissions from the user; but because you still need the user to be able to read the data, you should leave him with SELECT permissions. To be able to create objects, the user will need to be a member of the db_ddladmin role. The db_owner role can create objects but has far more rights than what is needed.
5:	You are designing a custom application to access secure data. The Public Relations department needs to be able to read and update the data. PR will also be inserting new data. The PR department will also use Microsoft Excel to retrieve and chart data. You want to allow the department access only while using the two applications. No users have been granted access to the server yet. What should you do? A. Create a database role. Grant SELECT, INSERT, and UPDATE to the role. Give the users server access. Assign the users to the role. B. Create an application role. Grant SELECT, INSERT, and UPDATE to the role. Have the two applications invoke the role. C. Give the users access to the server. Grant SELECT, INSERT, and UPDATE to each of the users. Deny DELETE to each of the users. D. Create a system role. Grant SELECT, INSERT, and UPDATE to the role.
A5:	Answer: B. To ensure that the users can make changes only from within the two applications, the applications will need to run under the context of an application role that handles the security. The other solutions do nothing to prevent access to the data using the permissions given through ODBC or other connectivity mechanisms. Also, it is not possible to create system roles.
6:	Public Relations and Human Resources require SELECT, INSERT, and UPDATE permissions on all tables in the Company database. Public Relations employees belong to a Microsoft Windows 2000 group named PR, and Human Resources employees belong to a Windows 2000 group named HR. You want to ensure that only members of HR can remove data. Other permissions may already exist. What should you do? A. Create two database roles named HR and PR. Add the appropriate Windows 2000 groups to the user-defined database roles. Deny DELETE to PR. B. Create an HR database role. Grant DELETE permissions to the role. Grant SELECT, INSERT, and UPDATE permissions to the Windows 2000 PR group. C. Grant DELETE to HR. Revoke DELETE from Public. Grant SELECT, INSERT, and UPDATE to PR. D. Create two database roles named HR and PR. Add the appropriate Windows 2000 groups to the user-defined database roles. Revoke DELETE to PR.
A6:	Answer: C. Because some permissions have already been defined, you will need to revoke the DELETE permissions from the Public role to remove any permission previously defined. You do not want to deny DELETE to Public because every user in the database is a member of Public and this would effectively eliminate the possibility of anyone performing deletions. You then need to assign the permissions to the appropriate Windows 2000 groups. In this scenario it is not necessary to create roles.