I came across an issue some time ago when a job of mine was regularly failing with a strange error - Unable to open the physical file “\\<path>”. Operating system error 5: "5(error not found)". The job in question ran monthly, and took a backup of our Data Warehouse database to provide us with a month end view of the data (we use Standard edition so database snapshots were not available to us). The Month End database files were stored on a different server.
The job consisted of four steps:
- Backup the Data Warehouse database
- Attach the Month End database
- Restore the backup to the Month End database
- Detach the Month End database
(Note: at this point I know there will be people desperate to point out the problems with the above approach, for example detaching a database clears the entire plan cache for the SQL Server Instance - not a good thing! I certainly wouldn't recommend using the above method, however our system and infrastructure setup was such that it served our purposes without any ill effects)
The job was failing on step 2 - attaching the Month End database. Looking at the file permissions of the Month End database files, only my personal account had permission to do anything. This was odd, but I didn't think much of it and simply added the SQL Agent account to the file permissions and restarted the job from step 2.
The following month the failure reoccurred. I checked the file permissions and once again only my personal account was listed. The reason? During the course of the month I had user requests for month end data, leading me to manually attach the Month End database, deal with the request, then manually detach it. We couldn't leave it attached due to our 3rd party backup software which backed up every available database each night, meaning if attached, the Month End database would be unnecessarily backed up - an overhead we couldn't afford. So, on detach, the database files were being stripped of all permissions except my own.
On detaching the database, SQL Server attempts to impersonate the Windows account giving the command, adds the account to the file permissions allowing full control for that account, then removes all other permissions. This is by design for security purposes. It stops users who have full permissions to the folder, but not necessarily the database, from tampering with the database files. For mixed mode authentication, SQL Server cannot impersonate the user for this action and instead provides full control to the SQL Server service account, as well as members of the local Windows administrators group.
Other database actions also affect the file permissions, such as backup/restore or creating a new filegroup, and all are summarised in a nice little BOL article here: http://msdn.microsoft.com/en-us/library/ms189128%28SQL.90%29.aspx