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:

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


April 6, 2006

1. Perspectives

  • Required Reading: SQL Server 2005 Security Changes
  • 2. SQL Server 2005 Watch

  • Microsoft Announces Plans for Summer CTP of SQL Server Everywhere Edition
  • 3. News & Views

  • Editor's Note: Meet IT Experts at Connections Europe
  • Results of Previous Instant Poll: Made in Express
  • New Instant Poll: SQL Server Connections
  • 4. Events and Resources

  • Near Real-Time BI with SQL Server 2005
  • 10 Tips for Upgrading to SQL Server 2005
  • Manage Email Security
  • DevConnections Europe, 24-27 April in Nice, France
  • 5. Featured White Paper

  • Exchange Backup and Recovery
  • 6. Peer to Peer

  • Hot Tip: How Not to Use USE
  • Hot Article: SQL Server 2005 Builds
  • In a Nutshell: SQL Server 2005 System Diagram
  • Hot Threads
  • 7. Announcements

  • Exclusive Spring Savings
  • Save 44% On the Windows Scripting Solutions Newsletter
  • 8. New & Improved

  • Free Tool for Database Comparison
  • Audit User Activity Across Multiple Database Platforms

  • 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.

    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.

    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!

    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):

  • 14% Yes. I could use $10,000!
  • 0% Yes. It sounds like a fun challenge.
  • 22% No. It's a cool idea, but I won't participate.
  • 49% No. I don't have time for extra projects.
  • 16% No, I'm not into contests.
  • 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

  • Yes, I always attend
  • Yes, it was my first time
  • No, but I plan to attend this fall
  • No, but I plan to attend the Europe Connections show
  • No, and I'm not planning to

  • 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

    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

    Learn the best ways to manage your email security (and fight spam) using a variety of solutions and tips.

    Special Offer Ends Soon! Register now for DevConnections Europe, 24-27 April in Nice, France, and get a second registration for half price.

    See the complete Windows IT Pro Network guide to Web and live events.

    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.

    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

    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

    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

    Hot Threads:
    Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.

  • Administration: SQL Server Job Security
  • T-SQL: Can Sp_executesql Save Its Results to Variables?
  • Development: Creating Excel Report from Dynamic Table
  • Security: Security Access to Run a DTS Package

  • Hot Spot
    Learn the 12 critical capabilities needed in your virtual lab automation solution to meet the increasing demands from virtualization technology.

    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:

    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:

    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.

    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.

    Contact Us

  • About the newsletter—[email protected]
  • About SQL Server Perspectives—[email protected]
  • About technical questions—http://www.sqlmag.com/forums
  • About product news—[email protected]
  • About your subscription—[email protected]
  • About sponsoring SQL Server Magazine UPDATE—Richard Resnick, [email protected]
  • 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.

    Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.