The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

12.8. Summary

The goal of this chapter is to highlight the most important security issues for a DW/BI system, and to help you figure out where and when to secure data. We couldn't possibly cover all the security features of SQL Server, nor all the details of how to implement those features. We hope merely to have provided you with the tools to develop a security plan and security test plan.

One of the most important steps you can take for a secure DW/BI system is to identify which DW/BI team member is in charge of security. That security manager will drive the development and implementation of the security plan.

The easiest pieces of the security plan have to do with physical security and operating system security. There's lots of information available about how to secure servers and the Windows operating system. You just have to commit to doing it.

The harder question, and the one to which we devoted most of this chapter, has to do with securing the data. As we described, securing predefined reports in Reporting Services is easy, painless, and effective. Securing data for ad hoc analysis is a harder problem. You'll definitely find it easier, and the user experience much better, to define security rules in Analysis Services than in the relational database. Analysis Services' security features make this a fairly straightforward task for a wide range of requirements. And because security rules are defined in MDX, even very complicated scenarios are feasible.

Finally, we struggled ...