Review

3 4

The following questions are intended to reinforce key information presented in this chapter. If you are unable to answer a question, review the appropriate lesson and then try the question again. You can find answers to the questions in Appendix A, "Questions and Answers."

1. Which two layers of the security architecture outlined in Lesson 1 contain SQL Server-specific security features?
2. You specifically revoke the UPDATE permission from User01 for Table01 so that the user cannot update the table. After revoking the permission, User01 still can update Table01. Name three possible reasons for this result. Four possible reasons are included in the answers.
3. You create a view and grant the Public role SELECT permission to the view. A user attempts to run the view but receives a permission conflict error message. What is the most likely reason for this result?
4. You use sp_revokelogin, but a Windows user is still able to authenticate to SQL Server. What is the most likely reason why the user can authenticate to SQL Server?
5. A SQL Server computer is part of a workgroup. User01 on another Windows computer that is part of the same workgroup wants to log in to SQL Server. Name two ways that the user can connect to SQL Server.
6. You use sp_droplogin to delete a SQL Server login ID from SQL Server, but you receive an error message stating that the security account is mapped or aliased to a database. You then use sp_revokelogin to delete a Windows account from SQL Server. You know that the Windows user account is mapped to several databases, but the procedure succeeds. Explain why you can delete the Windows account but you cannot delete the SQL Server login ID.
7. Why is there no place in Enterprise Manager to make a Windows user account or SQL Server login ID a member of a Windows group?
8. You map the special Guest account to a database named DB01, but you don't grant the Guest account any privileges in the database. You then run SQL Profiler and notice that a user who is not mapped to DB01 deleted a table in the database. What is the most likely reason why the user could delete a table?
9. You use the DENY statement to explicitly deny User01 and DOMAIN01\IT Users the CREATE VIEW permission. What Transact-SQL statement do you use to clear the explicit denial?
10. Using Enterprise Manager, you attempt to modify the permissions of a standard database role, but the Permissions button is inactive. You are connected to the database as a member of the SysAdmin role. Why is the Permissions button unavailable?
11. What are the next important steps after implementing a security design?
12. Explain the purpose of an application role and how you activate it.