June 2, 2011 at 1:03 pm
When I restore a database from a scheduled job, the permissions on the resulting database files (the actual .mdf and .ldf) do not inherit from the parent folder. Instead, the owner and only permission granted is the account that the SQL Server service starts as. In my case, that's a domain user, but in testing, it does the same thing when the service startup account is 'Network Service'.
Is there any way to restore a database from a scheduled job and retain the permissions it should inherit from the folder?
A little background: this is part of a custom disaster recovery plan. A stored procedure runs as a scheduled job, and makes a backup of all databases. The resulting .bak files do inherit permissions correctly, btw. It then restores the system databases (master, etc.) from those backups to alternate names (e.g. DRmaster, etc.), and then detaches them, leaving an unlocked, flat-file duplicate of the system dbs that our nightly file backup can pick up. Then, in case of a full disaster recovery, you don't have to mess with figuring out which version of SQL (2000-2008) this is, and what procedure you need to follow to rebuild master, all at 2AM. You just restore files, and then simply rename the flat files that have just been restored to the \Data\ folder to master.mdf etc, and start SQL. I actually leave a .bat file in that folder to automate that for the groggy brain of the on-call tech. Done and done. Except that those files deny local admins permission, so you have to take ownership, push inheritance, then rename. Not awful, but I'd like to avoid it, especially at 2AM.
June 3, 2011 at 8:34 am
Update: This is only happening on SQL 2005 and 2008, not on the few SQL 2000 servers we still maintain.
It also only occurs using the WITH MOVE parameter of RESTORE DATABASE. If I modify the script to back up a test database and restore over top of it, the permissions are correct. I also modified it to drop the database before restoring it, so that it couldn't simply use the existing permissions of the files, and the new files that are created by that statement do inherit the correct permission set.
2nd update:
I haven't found a solution, only a crude workaround, which is to use the built-in command line 'takeown' and 'cacls' to reset permissions the way they should have been set by SQL. I'm running those via xp_cmdshell after the restores finish.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply