Security Changes in SQL Server 2008

SQL Server 2008, among other introduced significant security changes that enhance the database administrators applying an even stricter security policy on the SQL Server instances. This article discusses the security changes in SQL Server 2008.

Major Security Changes in SQL Server 2008

Besides the changes in the Windows local groups that are created during the SQL Server setup process, the changes in surface control tools where now you can use the Policy-Based Management feature that is much more powerful than the Surface Area Configuration tool, and the Kerberos support for named pipes and shared memory protocols, the change that has a strong effect on the way that many of us used to work, is that the local Windows Group BUILTIN Administrators is no longer by default included in the SQL Server sysadmin fixed server role on new SQL Server 2008 and R2 installations.

So, what does this mean? It means that if you try to access a SQL Server 2008 (or later) instance using a local administrator user account without explicitly granting him the sysadmin server role on the instance, you will not be able to have administrative rights on the instance. Actually, if this user has not any permissions on the specific instance, he will not be able to access the instance at all!

Someone might say that this makes things more complicated but the truth is that it does not. It is an excellent security enhancement that actually separates Windows administrator accounts from SQL Server administrators.

And by the way be careful when you install and perform the initial setup of a SQL Server 2008 (or later) instance because if you don’t include at least one user in the sysadmin role, you will be locked out of that instance 🙂

For more information on the security changes in SQL Server 2008 and SQL Server 2008 R2 you can visit this MSDN Library article. Make sure that you read it before configuring the security of your new SQL Server instance!