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:
- Have the permissions set on start up, after the ephemeral drive has been formatted but before SQL Server starts (more preferable).
- Add MSSQLSERVER to the administrators group (less preferable).
Are there any pointers to how to set drive permissions after formatting but before system services?