Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

CHAPTER 4 Security and Compliance Security is important--more so, in fact, than design, creation, and performance. If your database had no security measures in place, absolutely anyone could come along and steal or corrupt the data, causing havoc to you and your company--and not in just one database but in every database in every server. Security can be enforced in many ways on SQL Server: by Windows itself through Windows authentication; by restricting users' access to sensitive data through views; by specifically creating users, logins, and roles that have explicit levels of access; or by encrypting your database, logs, and files. This chapter covers some parts of security, although it is impossible to talk about every area of security, mainly because you haven't seen much of SQL Server's feature set yet! In Chapter 1, you looked at the difference between Windows authentication and SQL Server authentication, so already you know your options with regard to the type of security you might want to use. In this chapter, you'll go deeper. You will see security mentioned at some other points in the book where relevant to give you a good level of knowledge about securing your database solution. You will also see a section in Chapter 12 of the book once many of SQL Server's features have been covered. You will also learn about Declarative Management Framework (DMF), which was new in SQL Server 2008 and improved in SQL Server 2012. In the past, a database administrator had to write tools and monitor systems to ensure that any new database created in production was created with the right options. The database administrator also had to monitor to ensure that items were not being created when they should not be. Now it is possible to create rules on databases, tables, views, and so on to check whether an object in question is compliant with standards defined by those rules. If an object isn't, then you will be notified, and a simple click will allow changes to be made to bring the object back into alignment with the policy. By the end of this chapter, you will have dealt with the following: · · · · Logins for individuals and groups Roles at the server, database, and application level Schemas Applying policies to your database to ensure compliance So the first area of security and compliance you need to understand is what roles and logins are. Logins The only way anyone can connect to SQL Server is via a login. As discussed in Chapter 1, this doesn't necessarily mean that every user has to have a specific login within SQL Server itself. With Windows authentication, if a Windows user belongs to a specific Windows group, just by belonging to that group, 101