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:
- Right click the catalog folder name under SQLServerInstallPath \Microsoft SQL Server\ MSSQL.X \MSSQL\FTDATA\ and choose Properties.
- In the properties window, select the Security tab
- Click Edit button.
- For the "Permissions for <Catalog Name> Folder," click the Add button.
- For the "Select Users, Computers, or Groups" Window, change the location to be the local machine name.
- For "Enter the object names to select", enter <macine name>\SQLServer2005MSFTEUser$<machine name>$MSSQLSERVER.
- Click Check Names to verify the name resolves to the correct group.
- Click OK to save the change.
- Click OK to close the "Permissions for <Catalog Name> Folder.
Steps to reproduce:
- In SQL 2005, Create a New catalog on a database.
- Go to FTDATA folder and view properties of the new catalog folder. You will see that we granted permissions to both SQLServer2005MSFTEUser and SQLServer2005MSSQLUser.
- Create a full-text index.
- Backup the database.
- Delete the database.
- Restore the database.
- View the permissions on the catalog folder in FTDATA.
Result: The SQLServer2005MSFTEUser account is NOT listed. Just SQLServer2005MSSQLUser