This is a more extended writeup of running Windows SharePoint Services 2003 and SQL Full Text Search on a Database box where Local Administrators (BUILTIN\Administrators) don’t have System Admin access in SQL Server 2000. (I mentioned this briefly in the Changing SharePoint Service Accounts article.)

Essentially, you’ll run up against this security policy requirement in some environments. It’s a sensible policy to make in situations/operations where the Local Administrators (of whom many are also Domain Administrators) are folks who are different from the folks who own, run and are responsible for the SQL Servers.

Part of the motivation for this separation is, of course, political. In some organizations you’ll find that folks in one team don’t want to share permissions/rights with other teams who aren’t directly responsible for the upkeep or maintenance of the bit of the sandbox they have dominion over.

The Sensible Computer Security Policy reason is the principle of Least Privileges. When the question, “Do these people/does this group need permissions to this resource?” is answered “No.”, then the principle of Least Privileges dictates that they not be given the access they don’t need. This Security Principle falls under the overall category of Risk Management. The fewer potential risks (i.e. fewer accounts sitting around waiting to be hacked that have permissions they don’t necessarily need), the fewer potential security vulnerabilities sit around waiting to be exploited by Joe Q. Attacker.

It should be noted that in the annals of computer attackers, the long-neglected account that just happens to be a local or domain administrator and just happens to have a really easy to guess password is the holy grail, and almost every computer system has at least one. So do what you can to manage your risks and reduce the number of holy grails that attackers can use to compromise your system.

Anyway, so for whatever reasons, you’ve decided that you wish to implement the policy that Local Administrators on the SQL Server are not allowed to be System Administrators (aka sa) within the SQL Server/Application itself. Note that while it appears that Microsoft “supports” this configuration, it’s not specifically allowed for in Microsoft’s relevant Knowledge Base articles, so if you do go this way, be on the lookout for potential complications. See that other article I mentioned and linked to above for an example of an unexpected consequence.

If you remove BUILTIN\Administrators from your SharePoint 2003 server’s SQL Server Logins, or remove the sa permissions from that group, you will hose up your Full Text Search in SQL Server, which of course (say it with me) will screw up your Full Text Search in your Windows SharePoint Services 2003 sites. (Because Windows SharePoint Services 2003 uses SQL Full Text Search to do its searching.)

How do you fix this?

According to KB Article 317746, if you don’t wish to add BUILTIN\Administrators back to the SQL Server Logins, you still have an out. You must:

• Add the System Administrators Server Role to the account you are using as the Service Account for SQL Server.
• Add the Local System account (NT AUTHORITY\System) to the SQL Server Logins.
• Add the System Administrators Server Role to the Local System account (NT AUTHORITY\System).

You should not have to restart SQL Server after making this change. But you may also need to fix Full Text Search for other reasons, which I will elucidate in a (shortly to follow) article.