SQL Engine Services and Domain Accounts Used as the Service Login

Question

Looking for detailed information on what resources the MS SQL Engine reads, writes, and modifies in order to operate as designed.  Read this carefully.  I've been on the search but am not finding anything quickly.

I believe if a Windows domain account is being used for any of the SQL Engine services that account should not have any granular permission settings on any folders related to SQL operations, nor any granular settings on the registry or any where else.  Should be FULL CONTROL.  Microsoft wrote the program to run the way it does and we should not be the judge of what permissoins a service account should have so the program runs correctly.

I am troubleshooting unstable SQL issues and I am finding little tips here and there pointing in my mind to be the service account having too many restrictions on the SQL folders, Windows registry, and everything else.

My opinion would be, any domain account used as a SQL service account should have FULL CONTROLL on any folders used for SQL operations and FULL CONTROLL on registry operations and be part of NO group policies.

If anyone knows where I may find detailed information that is written in GOLD as to what permissions SQL needs to run, let me know.  Send a link.  I think there should be NO restrictions.  I haven't met anybody yet that really understands the 10,000 possibilties of setting persmissions that tell me restricting SQL service accounts is a good thing.

Thank you all.  Just struggling in a limited resource department trying to do well like everybosy else.

 

 

 

 

 

 

---

Scrambling I.T. Guy WILD WILD WEST SHOW

Answer 1

Did you check this http://msdn.microsoft.com/en-us/library/ms143504.aspx

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

.. any domain account used as a SQL service account should have FULL CONTROLL on any folders used for SQL operations and FULL CONTROLL on registry operations and be part of NO group policies ....

If anyone knows where I may find detailed information

---

Scrambling I.T. Guy WILD WILD WEST SHOW

I do not agree with you because of security problems concerning the controlling of access privileges to a folder which will be controlled by the sql server. You have to think about the covering problems because if you run xp_cmdshell with NO proxy you will act as the service account!

If you grant [FULL PERMISSION] to folders it means that you can control these permissions by simple t-sql in conjunction with xp_cmdshell!
I'm working in a bank and for any of these suggestions to the it management I would loose my job ;-)

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de

Answer 3

To be precise for permissions needed by SQL Server Service Account is as follows:

Instance_ID\MSSQL\Log should have Full control
Instance_ID\MSSQL\backup should have Full control
Instance_ID\MSSQL\binn should have Read, Execute
Instance_ID\MSSQL\FTData should have Full control
Instance_ID\MSSQL\Install should have Read, Execute
Instance_ID\MSSQL\Repldata should have Full control
100\shared should have Read, Execute
Instance_ID\MSSQL\data should have Full control
For SQL Server Express, Instance_ID\MSSQL\Template Data should have Read

Hope that helps. But if you are facing any specific issue then please let us know.

Answer 4

The Books Online reference that RamJaddu mentioned in our best official documentation. http://msdn.microsoft.com/en-us/library/ms143504.aspx
We are trying to do a more comprehensive job of this for SQL Server Denali and you can see that here http://msdn.microsoft.com/en-us/library/ms143504(SQL.110).aspx The system changed in Denali, so it doesn't directly apply to your SQL Server 2008 R2. I am referring you to the Denali documentation because it can give you a better idea of the types of permissions that are set, which I hope you find helpful. But I regret that I don't have that level of detail for SQL Server 2008. I hope you find this more helpful than annoying.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 5

The Books Online reference that RamJaddu mentioned in our best official documentation. http://msdn.microsoft.com/en-us/library/ms143504.aspx
We are trying to do a more comprehensive job of this for SQL Server Denali and you can see that here http://msdn.microsoft.com/en-us/library/ms143504(SQL.110).aspx The system changed in Denali, so it doesn't directly apply to your SQL Server 2008 R2. I am referring you to the Denali documentation because it can give you a better idea of the types of permissions that are set, which I hope you find helpful. But I regret that I don't have that level of detail for SQL Server 2008. I hope you find this more helpful than annoying.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Thanks.  Everyone's input is great.  I'm on a road to resolution.

---

Scrambling I.T. Guy WILD WILD WEST SHOW

Answer 6

All your input was great.  I collected everything I needed and turned it in.  Did my part now I just hope my peers listen and can get this stuff starightened out.  Did all I can do but...it's out of my hands now.  So frustrating.

You all get 1000 points from me.  Thanks.

---

Scrambling I.T. Guy WILD WILD WEST SHOW