I have an Amazon EC2 instance which is used for long SQL Server queries but is stopped in between. I use the SSD ephemeral drives for tempdb but whenever the instance is started from a stopped state, the MSSQLSERVER user doesn't have permissions to the ephemeral drives and I have to manually run icacls then start the SQL Server service. To end this manual intervention, it seems my choices are to:

  1. Have the permissions set on start up, after the ephemeral drive has been formatted but before SQL Server starts (more preferable).
  2. Add MSSQLSERVER to the administrators group (less preferable).

Are there any pointers to how to set drive permissions after formatting but before system services?


I solved it myself thanks. I set the SQL Server service start up type to Automatic (Delayed Start) and added an event in the Task Scheduler delayed by 1 minute to run icacls.exe on the new ephemeral drives. I might make it a more complex batch or PowerShell script that determines the number of instance store drives and spreads the tempdb appropriately but for now it only stands at 1 anyway.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.