SQL Server Magazine UPDATE, April 6, 2006--Required Reading: SQL Server 2005 Security Changes

In This Issue:
—Changes to the SQL Server 2005 Security model might not be as sexy as other new features of the new release, but understanding security changes should be every DBA's top priority.
—Microsoft Announces SQL Server Everywhere Edition

Subscribe to SQL Server Magazine:
http://lists.sqlmag.com/t?ctl=26515:7B3DA

To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

This Issue Sponsored By
This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

Double-Take Software
http://lists.sqlmag.com/t?ctl=26507:7B3DA

AppDev
http://lists.sqlmag.com/t?ctl=26519:7B3DA

April 6, 2006

1. Perspectives

2. SQL Server 2005 Watch

3. News & Views

4. Events and Resources

5. Featured White Paper

6. Peer to Peer

7. Announcements

8. New & Improved

Sponsor: Double-Take Software
Start your disaster recovery program before it's too late—including defining the right plan for you, and mistakes to avoid when planning.
http://lists.sqlmag.com/t?ctl=26507:7B3DA

1. Perspectives

Required Reading: SQL Server 2005 Security Changes
by Brian Moran, [email protected]

Sexy, exotic new SQL Server 2005 features such as the SQLCLR, Service Broker, endpoints, and SQL Server Integration Services (SSIS) have gotten a lot of press time. However, core security-model changes are some of the most important changes in the product—and they seem to have been a bit under-reviewed during the upgrade cycle. My space in this editorial is limited, so, I'm not going to dive deep into the specifics of what's changed. But you need to realize that the security model has undergone changes that you must understand before moving your SQL Server 2000 applications to SQL Server 2005.

The SQL Server 2000 security model didn't get a big name change (e.g., DTS to SSIS), but the SQL Server 2005 security model is different from SQL Server 2000 in many core ways. DBAs who are upgrading to SQL Server 2005 can choose to disable the SQLCLR or other new features until they better understand them. But you can't disable security, and you certainly need to be familiar with the changes before moving to production.

The SQL Server 2000 security model is pretty simple. We have logins, users, roles, and a relatively limited number of base permissions. SQL Server 2005 adds the key security model concepts of Principals and Securables, and the base permission list has grown to almost 200 distinct permissions. Granting a user some of these permissions in turn also grants the user derived or implicit access to other permissions. Thus, it's important to review the changes so that you thoroughly understand the relationships among permissions.

Other changes will also spark your interest and get you digging deeper into the security changes. For example, SQL Server 2000 had the concept of a schema as it related to object ownership and definition, but do you know that schemas in SQL Server 2005 are vastly different? And do you know that the EXECUTE AS statement gives you a way to manage security of dynamic T-SQL within a stored procedure?

You can review a comprehensive set of the changes directly from the SQL Server 2005 Books Online (BOL) topic "Security Considerations for Databases and Database Applications" at http://msdn2.microsoft.com/en-us/library/ms187648.aspx. If you want more information, a 15-second Internet search will yield a rich collection of other resources about SQL Server 2005 security.

A typical DBA can get by without knowing many of the newest SQL Server 2005 features during the early phases of an upgrade. However, no DBA can run the risk of making a serious error by not being familiar with the security changes in SQL Server 2005.

Sponsor: AppDev
Learn SQL Server 2005 now—Get a FREE training CD!
Start learning SQL Server 2005 today with cutting edge training from AppDev. Get a FREE SQL 2005 training CD from our new course (a $115 value). Click the link below for your FREE SQL Server 2005 training.
http://lists.sqlmag.com/t?ctl=26519:7B3DA

2. SQL Server 2005 Watch

Microsoft Announces Plans for Summer CTP of SQL Server Everywhere Edition

Today, Microsoft announced plans for a summer Consumer Technology Preview (CTP) release of its new SQL Server Everywhere Edition. The new offering will specifically promote building client applications that operate in "occasionally connected" environments. The new edition, which shares a common programming model with all other SQL Server editions, will provide a "lightweight, compact, but rich subset of the capabilities found in other SQL Server editions. Beyond having rich local data management capabilities, SQL Server Everywhere Edition will also include support for seamlessly synchronizing with other SQL Server editions," according to Paul Flessner, Microsoft Senior Vice President for the Data and Storage Platform Division. The company plans a final release of SQL Server Everywhere Edition before the end of the calendar year.

The announcement of the new SQL Server edition comes as part of a general public announcement in which Flessner detailed Microsoft's plans for significant investment in its data-management products. Central to Microsoft's data-management vision, dubbed "Your Data, Any Place, Any Time," are what Flessner calls SQL Server AlwaysOn Technologies. These technologies--such as SQL Server 2005's database mirroring, failover clustering, database snapshots, and enhanced online operations—let customers achieve and maintain high application availability. Flessner's announcement stresses that such technologies will continue to be enhanced in upcoming SQL Server releases, which Microsoft has committed to providing every 24 to 36 months. You can read Flessner's complete update at http://www.microsoft.com/sql/letter.mspx.

3. News & Views

Editor's Note

Did you know your favorite Connections conference is coming to Europe in April? Learn from your favorite authors live and in person, and hear directly from Microsoft experts about the next generation of Microsoft technologies. This is an action-packed event with four conferences located together for one rate: ASP.NET, Visual Studio, SQL Server and Exchange plus bonus sessions on Sharepoint and Windows! I'm going to let you know about a special rate—when you buy your first conference registration at 1,100 euros, you can get additional passes at half off—so partner up with your friends and take advantage of this great rate. The regular price is 1450 euros, so this is a big bargain, especially when you check out the lineup of speakers! To get your special rate, go to the following site to register today and enter promocode: EXPENL. Hope to see you in Nice!
http://lists.sqlmag.com/t?ctl=26517:7B3DA

Results of Previous Instant Poll: Made in Express
"Will you enter the Microsoft Made in Express contest?" Here are the results from the 51 votes (deviations from 100 are due to a rounding error):

New Instant Poll: SQL Server Connections
"Did you attend this week's Connections conference in Orlando, Florida?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=2651F:7B3DA ) and submit your vote for

4. Events and Resources

Learn to incorporate new services found in SQL Server 2005 to create near-real-time BI information without the need for a data warehouse. Live Event: Thursday, April 20
http://lists.sqlmag.com/t?ctl=26508:7B3DA

Learn the 10 essential tips you need to know when upgrading to SQL Server 2005, including new features designed to help ease the process. Live event: Wednesday, April 26
http://lists.sqlmag.com/t?ctl=2650A:7B3DA

Learn the best ways to manage your email security (and fight spam) using a variety of solutions and tips.
http://lists.sqlmag.com/t?ctl=26509:7B3DA

Special Offer Ends Soon! Register now for DevConnections Europe, 24-27 April in Nice, France, and get a second registration for half price.
http://lists.sqlmag.com/t?ctl=26511:7B3DA

See the complete Windows IT Pro Network guide to Web and live events.
http://lists.sqlmag.com/t?ctl=2651A:7B3DA

5. Featured White Paper

Protect mission-critical business information stored on your high-availability Exchange systems when you implement backup and restore strategies. You'll also learn about key configuration and deployment considerations.
http://lists.sqlmag.com/t?ctl=2650C:7B3DA

6. Peer to Peer

Hot Tip:
How Not to Use USE
by Microsoft's SQL Server Development Team, [email protected]
USE is not a T-SQL statement like GO; only tools such as ISQL, OSQL, SQLCMD, Query Analyzer, and SQL Server 2005 Management Studio understand it. Here's a T-SQL procedure that doesn't use USE to retrieve table information from databases.
Read this tip today at
http://lists.sqlmag.com/t?ctl=26510:7B3DA

Hot Article:
SQL Server 2005 Builds
You might have a hard time visually distinguishing the different prerelease versions of SQL Server from the final release. In his April SELECT TOP(X) column "SQL Server 2005 Builds," Michael Otey helps you figure out which SQL Server 2005 versions you're running in your environment. Read this article today and post your comments at
http://lists.sqlmag.com/t?ctl=2650F:7B3DA

In a Nutshell:
SQL Server 2005 System Diagram
In this week's blog, Keving tells you where to download a PDF of the system table map for SQL Server 2005—and how to get your own, glossy poster to put on your wall. Read the blog and let Kevin know your opinion today at
http://lists.sqlmag.com/t?ctl=26512:7B3DA

Hot Threads:
Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.
http://lists.sqlmag.com/t?ctl=26516:7B3DA

Hot Spot
Learn the 12 critical capabilities needed in your virtual lab automation solution to meet the increasing demands from virtualization technology.
http://lists.sqlmag.com/t?ctl=2650B:7B3DA

7. Announcements

Exclusive Spring Savings
Subscribe to SQL Server Magazine and SAVE 58%! Along with your 12 issues, you'll get FREE access to the entire SQL Server Magazine online article archive, which houses more than 2,300 helpful articles. This is a limited-time offer, so order now:
http://lists.sqlmag.com/t?ctl=2650E:7B3DA

Save 44% On the Windows Scripting Solutions Newsletter
For a limited-time, order the Windows Scripting Solutions newsletter and SAVE up to $80. You'll get 12 helpful issues loaded with expert-reviewed downloadable code and scripting techniques, as well as hundreds of tips on automating repetitive tasks. You'll also get FREE, unlimited access to the full online scripting article library (more than 500 articles). Subscribe now:
http://lists.sqlmag.com/t?ctl=2650D:7B3DA

8. New & Improved

by Blake Eno, [email protected]

Free Tool for Database Comparison
SQL Effects Software announced SQL Effects Clarity Community Edition (CE), a free database-comparison tool that lets you view your database-comparison results in a side-by-side tree view. You can also drill-down for further details. SQL Effects features full support for SQL Server objects and configurable comparison settings (object and attribute level). For more information, contact SQL Effects Software at 206-222-2775.
http://lists.sqlmag.com/t?ctl=2651D:7B3DA

Audit User Activity Across Multiple Database Platforms
Consul announced the Consul InSight Suite, a compliance monitoring and reporting solution for Oracle, SQL Server, IBM DB2, IBM UDB, and Sybase. InSight monitors both trusted and privileged user activity and delivers customized audit reports through its dashboard. The product includes hundreds of reports that deliver information about sensitive data access, system access, privileged activity, and operational change control. Consul InSight can also extend its database auditing to applications, OSs, and security devices. Pricing for Consul Insight starts at $25,000. For more information, contact Consul at 925-236-5000 or 800-879-2273.
http://lists.sqlmag.com/t?ctl=2651E:7B3DA

Contact Us

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today!

View the SQL Server Magazine Privacy Policy.

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street, Loveland, CO 80538
Attention: Customer Service Department

Copyright 2006, Penton Media, Inc. All Rights Reserved.