The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005...
for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete book excerpt series or purchase the book.
New security features in SQL Server 2005
SQL Server 2005 adds new security features, not only to make SQL Server more secure, but to make security more understandable and easier to administer. Some of these features will permit programmers to develop database applications while running with the exact privileges that they need. This is known as "the principle of least privilege." No longer does every programmer need to run as database administrator or "sa." The major new features include the following.
- Security for .NET executable code -- Administration and execution of .NET code is managed through a combination of SQL Server permissions, Windows permissions, and .NET code security. What the code can or cannot do inside and outside SQL Server is defined with three distinct levels.
- Password policies for SQL server users -- If you run SQL Server 2005 on a Windows 2003 Server, SQL users can go by the same policies as integrated security users.
- Mapping SQL Server users to Windows credentials -- SQL Server users can use Windows credentials when accessing external resources like files and network shares.
- Separation of users and schemas -- SQL Server 2005 schemas are first-class objects that can be owned by a user, role, group, or application roles. The capability to define synonyms makes this easier to administer.
- Granting permissions -- No longer do users or logins have to be in special roles to have certain permissions; they are all grantable with the GRANT, DENY, and REVOKE verbs.
- New security on SQL Server metadata -- New metadata views are not directly updatable, and users can only list metadata about objects to which they have permission. There is also a new grantable VIEW DEFINITION permission.
- Execution context for procedural code -- You can set the execution context for stored procedures and user-defined functions. You can also use the EXECUTE AS syntax to change the current user.
- Support of certificates and encryption keys -- SQL Server 2005 can manage certificates and encryption keys for use with Service Broker, with Web Services SSL, for code authentication, and for new data encryption functions.
Some of the new security features are outside the scope of this book, and some were still in development at the time of this writing. But we'll look at most of them in detail in this chapter. Running non-T-SQL code in a secure and reliable fashion inside SQL Server is a new concept, and we will spend a lot of this chapter examining how SQL Server's new security features combine with the latest .NET runtime to make this possible. We include a review of how security currently works in SQL Server in general to establish a baseline of knowledge.
We will start by looking at a change in the general policy of how SQL Server is configured. Though having optional features turned off by default is technically not a feature as such, most will consider it an important step forward in securing SQL Server.
Click for the next excerpt in this series: Optional features are turned off by default