SQL server permissions

Question

Hi there,
Tried to find on the web the answer to my question but can't find someone asking the question or an explanation.

The question I have is;
Why does the SQL server service account ( in services.msc ), why does that account need Full access to the file (MDF) and folder to which the mdf belongs? I understand it needs to read and write, however if you want to attach a database it will only succeed if the service account has full access. Read/Write isn't enough and I would like to know why. I understand that the difference between full control and readwrite is that with full control you can set permissions, however DB permissions are not NTFS or share but within SQL and hence I don't understand why the service account needs full control.

Hopefully someone is able to clarify this for me, I don't think it is a big deal with MSA but I am just curious.

Answer 1

SQL Server is going to do more to that file than just read and write it. For example, in order for SQL to own that file it has to have take ownership rights. That comes with Full Control but not RW. It also needs to potentially change permissions. For the folder level it might need to create temp files to do things like backup or use file groups, etc.

So in theory you might be able to limit SQL Server to just needing specific permissions, in reality it needs a lot so rather than mandating a subset of full rights it is just easier to require full. This also fits into the early versions of SQL that used SYSTEM which has full rights anyway. Since this is a service and a malicious user isn't going to "secretly" install it then it makes sense that the service requires full control rather than a subset. Reduces the likelihood of a file system security error in that case.

Answer 2

Hi, thank you for your reply.
Your comment "SQL Server is going to do more to that file than just read and write it" is interesting, I haven't thought about that but do understand that it could create temp files.

Also interesting information regarding System, I am quite new to SQL and find your information very usefull.

However, I am still looking for a concrete example of why it needs full access. You wrote, it might create temp files in the folder but that is also possible with read/write permissions. Chaning permissions on the MDF file itself isn't being done by the service if I am correct ( please correct me if I am wrong ). The permissions are set within SQL itself and not on NTFS level.

So thanks again and would be great if someone can give me 1 concrete example of why it could need full access to the MDF file itself and the folder and where read/write won't be sufficient.

Answer 3

I did some testing and I was able to create a database which the service account did not own. Then I set that database offline and took ownership of the mdf file myself. Then I gave only READ-WRITE permissions to the service account and attempted to set the database online again, which failed with OS error 5, "Access is denied". I added Modify to the rights, but I got the same error. Then I added Full Control, and now it worked out.

I put the database offline again and checked the file owner - that was still me.

Why the OS thinks that the service account needs full control I don't know. And maybe not exactly Full Control is needed, but only of these special permissions which are not displayed by default. In any case, SQL Server writes to the database file in a quite different way from a program that just generates a text file and writes to it from start to end. It probably needs some special operations for things to go fast.

Answer 4

Hi @andre boom

Service accounts don't only need to just read or write into the files in the data/log/setup files folder but at times also need to create rename delete lock the files for example error logs setup log agent log or tempdb files(everytime sql server service restarts tempdb is recreated) when service is restarted, or when server is updated or patched etc.
Also the movement of sql server datafiles is locked when sql server service is in running state you cannot copy or move them to another folder without stopping the service first.

Regards,
Shivam

Answer 5

Hi ErlandSommarskog, thank you for taking the time to test it and confirm my findings. It is interesting isn't it? I agree it probably need some special permissions but that is also the idea of the question, I am just to curious of why. Maybe I shouldn't be that curious but would really like to know a specific exmample of why it needs the permissions.

Also thank you to ShivamKumar, thank you for replying. I understand it does more that just write into files in the data/log/setup folder as per your example, delete lock files for tempdb but that also can be done without full access and with just read/write. I understand that you can't move data files when the sql server service is in running state but that has nothing to, please correct me if I am wrong, with the NTFS permissions as you alo can't move it if you have full access as the files are occupied/locked by the service.

Thanks again to both of you and especially to ErlandSommarskog for taking the time to test my findings but my question remains "can someone give me a specific example of why SQL Service account needs full access instead of just modify /read-write?"

Best regards,

André