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!
Secure your SQL Server Instances with DBA Security Advisor
DBA Security Advisor, is our SQL Server security tool, which can help you assess your SQL Server instances against a rich set of security checks. The assessment report, includes recommendations and remediation scripts that can help you better secure your SQL Server instances and databases (learn more…).Try DBA Security Advisor free for 30 days!
Other SQL Server Security-Related Articles
- Why You Need to Secure Your SQL Server Instances
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- Frequent Password Expiration: Time to Revise it?
- Encrypting SQL Server Databases
- Transparent Data Encryption (TDE) in SQL Server
- 10 Facts About SQL Server Transparent Data Encryption
- Encrypting a SQL Server Database Backup
- Policy-Based Management in SQL Server
- …check all
Check our latest software releases!
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.