Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Guidelines on granting SQL Server sysadmin privileges

Guidelines on granting SQL Server sysadmin privileges

Summary

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.

↑ Back to the top

More Information

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 2008 R2

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

↑ Back to the top

Keywords: vkball, kb

↑ Back to the top

Article Info
Article ID : 2184138
Revision : 1
Created on : 1/8/2017
Published on : 4/3/2012
Exists online : False
Views : 120