Restoring Full-text Catalog in SQL Server 2005 removes SQLServer2005MSFTEUser ACL

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

When you restore a database with full-text catalog on a SQL Server 2005 server you will notice that the restore operation removes SQLServer2005MSFTEUser from the ACL list on the catalog folder in FTDATA.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
SQLServer2005MSFTEUser is a security group on the local server. You can manually add the ACL back to the folder using one of the following procedures.
  • Option 1: Detach and reattach the database. This will reset the ACL permissions correctly
  • Option 2: Manually add the pemisions using the following steps:
    1. Right click the catalog folder name under SQLServerInstallPath \Microsoft SQL Server\ MSSQL.X \MSSQL\FTDATA\ and choose Properties.
    2. In the properties window, select the Security tab
    3. Click Edit button.
    4. For the "Permissions for <Catalog Name> Folder," click the Add button.
    5. For the "Select Users, Computers, or Groups" Window, change the location to be the local machine name.
    6. For "Enter the object names to select", enter <macine name>\SQLServer2005MSFTEUser$<machine name>$MSSQLSERVER.
    7. Click Check Names to verify the name resolves to the correct group.
    8. Click OK to save the change.
    9. Click OK to close the "Permissions for <Catalog Name> Folder.

Steps to reproduce:
  1. In SQL 2005, Create a New catalog on a database.
  2. Go to FTDATA folder and view properties of the new catalog folder. You will see that we granted permissions to both SQLServer2005MSFTEUser and SQLServer2005MSSQLUser.
  3. Create a full-text index.
  4. Backup the database.
  5. Delete the database.
  6. Restore the database.
  7. View the permissions on the catalog folder in FTDATA.
    Result: The SQLServer2005MSFTEUser account is NOT listed. Just SQLServer2005MSSQLUser
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Article ID: 2413104 - Last Review: 09/20/2010 17:34:00 - Revision: 2.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Evaluation Edition, Microsoft SQL Server 2005 Everywhere Edition Community Technology Preview, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Workgroup Edition

  • KB2413104