If you worked -or still working- with SQL Server 2005 (or even earlier), you must have noticed that when you installed these SQL Server versions, the local Windows group “Built-In\Administrators” was automatically included in the SQL Server instance along with getting the role “SysAdmin” server role. This actually, when it comes to SQL Server security, is not a best practice.
For the above reason, from SQL Server 2008 and later this has stopped. One of the security changes in SQL Server 2008 (and later) was to stop automatically adding “Built-In\Administrators” as SQL Server SysAdmins during the SQL Server installation, thus leaving this decision to the person who performed the installation/setup of SQL Server and/or the Database Administrator (DBA), since it is a security risk for your SQL Server instance.
The above short introduction can easily lead us to the question: Should Windows “Built-In\Administrators” group be also SQL Server SysAdmins?
The above question has a definite answer based on SQL Server security best practices and that is No!
The above statement does not necessarily mean that a DBA cannot also have administrative access to the underlying machine onto which SQL Server is installed, but it basically suggests that the entire “Built-in\Administrators” group should never be included as “SysAdmins” in SQL Server. That is why SQL Server 2008 (and later) installation wizard does not automatically adds “Built-in\Administrators” as SQL Server SysAdmins anymore because in the end of the day, machine administrators and SQL Server administrators are two different roles that should not be mixed “by default”.
Another way to express the above concept in a single sentence is: A DBA can also be a machine administrator on a machine that has SQL Server installed on, but a machine administrator should not be a SQL Server SysAdmin.
*Important Note: At this point it is important to note that when installing SQL Server or handling security to never lock yourself out of the SQL Server instance. Always ensure that there is at least one active SysAdmin login mapped to a physical person (i.e. the DBA).
One of the available security checks in our SQL Server security tool “DBA Security Advisor“, is “Built-In\Administrators” access which checks and reports if there are any server roles assigned to the “Built-In\Administrators” group for the specified SQL Server instances.
Screenshot examples of checking the Built-In\Administrators access on a SQL Server 2017 instance:
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Featured Online Courses:
- AI Essentials: A Beginner’s Guide to Artificial Intelligence
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Other SQL Server Security-Related Articles
- DBA Security Advisor v2.3 is Now Out!
- How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
- Why You Need to Secure Your SQL Server Instances
- [DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).] SSL Security Error – How to Resolve
- SQL Server Row Level Security by Example
- Frequent Password Expiration: Time to Revise it?
- Policy-Based Management in SQL Server
- The “Public” Database Role in SQL Server
- Encrypting SQL Server Databases
- Transparent Data Encryption (TDE) in SQL Server
- Encrypting a SQL Server Database Backup
- What is Data Security and which are its Main Characteristics?
- …check all
Subscribe to our newsletter and stay up to date!
Subscribe to our YouTube channel (SQLNetHub TV)
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Generate dynamic T-SQL scripts with Dynamic SQL Generator!
Check our latest software releases!
Check our eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. 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. Moreover, Artemakis teaches on Udemy, you can check his courses here.