Beginning in SQL Server 2005, significant changes were implemented to make sure that SQL Server is more secure than earlier versions. Changes included a "secure by design, secure by default, and secure in deployment" strategy designed to help protect the server instance and its databases from security attacks.
This strategy is further enhanced in SQL Server 2008 and SQL Server 2008 R2. These enhancements further decrease the surface and attack area for SQL Server and its databases by instituting a policy of "Least Privileged" and increase the separation between the Windows Administrators and the SQL Server administrators.
In SQL Server 2008 and later, the local Windows Group BUILTIN\Administrator is no longer provisioned as a login in the SQL Server sysadmin fixed server role by default at SQL Server setup install. As a result, box administrators cannot login to the new SQL Server 2008 and SQL Server 2008 R2 instance by default.
With SQL Server 2008 and later, during new installation, one or more windows principals are required to be provisioned in the SQL Server sysadmin fixed server role. Carefully provision the logins in the sysadmin server role membership as this is a very privilege server role.
Summary
You can use the following query to determine the logins provisioned in the SQL Server sysadmin fixed server role.
SELECT
p.name AS [Name]
FROM
sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
WHERE r.type = 'R' and r.name = N'sysadmin'
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
|
Rule software
|
Rule title
|
Rule description
|
Product versions against which the rule is evaluated |
|
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) |
SQL Server Admin role membership check |
The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to determine the logins provisioned in the SQL Server sysadmin fixed server role. If you run the BPA tool and encounter warning with the title of Engine - SQL Server Admin role membership check,carefully evaluate the SQL Server sysadmin fixed server role membership. |
SQL Server 2008 |
|
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) |
SQL Server Admin role membership check |
The SQL Server 2012 Best Practice Analyzer (SQL Server 2012) provides a rule to determine the logins provisioned in the SQL Server sysadmin fixed server role. If you run the BPA tool and encounter warning with the title of Engine - SQL Server Admin role membership check,carefully evaluate the SQL Server sysadmin fixed server role membership. |
SQL Server 2012 |
