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 moreinformation, 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.