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

Symptoms

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.

Cause

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Workaround

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.

More Information

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