Sql-server – Why does SQL Server need permissions to access data and log files

permissionssql serverssms

When you want to attach a database, you can open them up if you put those mdf files in a folder such as:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

But SQL Server will not attach them if they are located on your desktop. If you go to your desktop path, e.g.:

C:\users\username\desktop\

It states that it cannot find the object because it does not exist or you do not have permissions. I checked the security of the folder that is located on my desktop and I have full access. Can anyone explain this?

Best Answer

The SQL Server service account needs to have read/write access to the folders where the data and log files exist. Because it needs to read and write.

By default the service account has access only to SQL Server's own folder structure. This has nothing to do with what access you have.

Your options are:

  • Grant explicit read/write access to your desired folder/tree
  • Change the service account to be you
  • Put the database files in their proper location for that instance

I prefer the latter so that you don't have active database files scattered all over your machine.

Related Question