Monday, July 30, 2012 12:52 PM
I know the steps to move system databases to a new location. What I don't know is how to give the correct permission to SQL Server services to the new location.
Now my system database are in "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"
I have a brand new disk G: and I want to recreate the directory tree and put the files into
"G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"
How to give the correct permission? My SQL Server starts with a domain account.
Monday, July 30, 2012 12:56 PMAnswerer
Monday, July 30, 2012 1:19 PMAdding account to the local administrators group? But now SQL Server service account isn't in the local administrators group...
Monday, July 30, 2012 1:39 PM
Add SQL Server to local admin account isn't best practice and is overkill.
Look at the old folder's security permissions in "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA" and see which accounts have which level of permissions/access. Then you should be able to recreate those permissions on your new directories.
Depending on how you are setup, SQL Server shouldn't need full access to the entire drive. I'd limit it to just the folders it needs access to.
You might see something like this and possible another one for the SQLAgent.
Here is the down and dirty on permissions.
- Marked As Answer by amber zhangModerator Tuesday, August 07, 2012 8:33 AM