Professional SQL Server™ 2005 Programming

Chapter 22. Security

There are probably as many ideas on security as there are programmers. It's one of those things where there isn't necessarily a right way to do it, but there are definitely plenty of wrong ones.

The first thing to understand about security is that there is no such thing as a totally secure application. If you can make it secure, rest assured that someone, somewhere, can defeat your efforts and "hack" into the system. Even with this knowledge, the goal still needs to be to keep unwanted intruders out of your system. The good news about security is that, for most instances, you can fairly easily make it such a hassle that 99.999 percent of people out there won't want to bother with it. For the other .001 percent, I can only encourage you to make sure that all your employees have a life so they fall into the 99.999 percent. The .001 percent will hopefully find someplace else to go.

With SQL Server 2005, Microsoft has gotten very serious about security for SQL Server. There are a ton of new features here, and, while there were already books that were specific to SQL Server security out there before, I can imagine them as being huge tomes now —the subject has grown that much with this release.

In this chapter, we're going to cover:

• Security basics

• SQL Server security options

• Database and server roles

• Application roles

• Credentials

• Certificates

• Schema management

• XML integration security issues

• More advanced security

What we'll discover is that there are a lot of different ...