Changes in SQL Server 2008 sysadmin group

There are some noteworthy changes in the way SQL Server 2008 handles security; apart from the single major improvement – the replacement of the Surface Area Configuration tool by Policy-Based Management.

One thing that surprised me today was that even though I was a Domain Admin and a member of the local Administrators group, SQL Server 2008 refused to let me log in. A login had to be explicitly created so I could access the instance. After some research, I found out that in SQL Server 2008 the local Windows administrators do not get mapped to the sysadmin role. Therefore, it is possible to get locked out of a server instance if there are no sysadmins on it. This is a feature, which separates more clearly SQL Server admins and Windows admins.

A further note on this topic. I would have not lost a small SQL Server war on a Dev environment recently if we were using SQL Server 2008 instead of SQL Server 2005. Now, being a Domain Admin does not necessarily win the battle for SQL Server permissions.

There is a TechNet page describing SQL Server 2008 Security Changes for further reference.

And another one, helping in case all system administrators are locked out.

Advertisements