SQL Server Security

You are the DBA for Acme Corporation. You’re asked to implement a new database server using Microsoft SQL Server. In any implementation, security needs to be a theme woven throughout the project, not an afterthought. Design an implementation that allows you to implement policies. The design should also include a role-based security structure. The business requirements for the database are as follows:

• No user should have delete access to any object.
• Human Resources needs to be able to fully manage employee data.
• Customer Service needs to be able to make changes to existing customers and view order data.
• Customer account managers need to be able to fully manage customer data and orders.
• Sales needs to be able to view customer data and fully manage orders.
• Marketing needs to able to pull quarterly sales numbers to analyze.

Follow these steps to produce a solution:

1. Install SQL Server using the Windows Integrated security mode.
2. Use a Window user for the service accounts.
3. Demote built-in\administrators to security and create a DBA windows group for system administrators.
4. Set up password policies based on best practices.
5. Use a minimum of eight characters
6. Use complex passwords
7. Set account lock-out for after three tries
8. Keep five passwords in history
9. Expire passwords after 30 days
10. Set up the roles listed in Tables 4-10 through 4-14.

Table 4-10 Human_Resources

Table 4-11 Customer_Service

Table 4-12 Account_Managers

Table 4-13 Sales

Table 4-14 Marketing