What are the pros and cons of giving SQL's service account administrator privileges?

Run the SQL service account under non-admin for SQL 2000 is tricky. I tried it once and it took a good deal of trial and error, setting rights for the custom account on registry, files, etc, etc. At the end of it , it seemed to work, but I hand no idea if it would always work in all scenarios.

For SQL 2005+, really there is no excuse. The MS SQL team provides configured roles on installation with the appropriate rights-- just drop in a non-admin account and voila, a MS approved way to run SQL without windows administrator rights.

If you have stored procedures or what have you that requires administrator rights, you should rewrite them, remove them, replace them. This is highly unlikely unless you are loading custom extended stored procedures or COM objects that do something that requires admin rights, or trying to run arbitrary code with xp_cmdshell.

As an Evil Hacker(TM), I'd say the biggest PRO: is being able to run fdisk via a SQL Injection attack :-)

Don't forget that the Slammer virus, which causes global internet failures was a SQL Server vulnerability.

Pro: full access to the local system

Con: full access to the local system

In addition, if the service account is also a domain account then SQL also has access to whatever network resources the account has access to which can be good or not. In SQL 2005+ you still have to specifically enable some of the features that present dangers down at the system level via the surface area configuration (xp_cmdshell, CLR, etc) but once they're enabled then....

Making it local admin is a lazy man's way of doing things, albeit much easier. In my opinion you have to weigh the risks. Much safer and best practice, though, to make it a non-privileged account and grant privileges as necessary. Use the principle of least privilege on the servers.

The major one is security, especially if you need something like xp_cmdshell for backward compatibility. It could make it easy to run any command on the machine.

Service accounts don't get there passwords changed to often and lead to another possible atack vector.

So the safest approach is to keep service accounts to the least privilege.

Security. I suggest contacting the vendor of the app that the DB is supporting and find out exactly what the service account needs to do. Sometimes vendors get lazy and just throw a blanket "requires root" in their documentation, but when pressed you discover that they only need certain insert rights.

Remember, root is very unforgiving.

I haven't built a SQL Server 2000 instance in years!..

For SQL Server 2005/2008, I don't even add it as a local user! Of course, the installer grants the user account log on as batch job privs. Then you have to give it privs to lock pages in memory and perform volume maintenance tasks. Also, the installer doesn't grant privs to any mount points that may be involved, so you should take care of that manually before you run the installer.

Only trouble I've run into is that the service account can't access ODBC DSNs. I'm sure there's a way around that, though...