Running SQL Server service using local account

Question

Running SQL Server service using local account

H-122523

Hall of Fame

Points: 3667
More actions
April 19, 2010 at 8:40 am

#219307

Hello all,
I am about to move my db server out of the domain, and need to run it under a local service account.
I can get the SQL Server Service (MSSQLSERVER) running by adding that local account to the local Administrator group, but that is far from best practice.
I came across a lot of references like 'only grant the necessary permissions to the local account', but nowhere a comprehensive list, so I am looking for a list of permissions for the local account.
Can anyone point me in the right direction please?
tia
Hans

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

H-122523 Hall of Fame Points: 3667 More actions · Answer 1

In the meantime, I found that I have to give NTFS Read&Execute permission on the MSSQL Binn folder, and full NTFS permission on the DATA and LOG folders if I want to start MSSQLSERVER service under a local account.

Has anyone found a combination of permissions that is less that the setup that I described here? All help is welcome.

regards,

Hans

george sibbald SSC Guru Points: 104210 More actions · Answer 2

did you use configuration manager to change the account SQL runs under. Doing it that way should assign all the permissions necessary.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

George has the best advice. Use the configuration manager to assign the account. It will assign the permissions needed.

H-122523 Hall of Fame Points: 3667 More actions · Answer 4

Yes, I used the configuration manager; when I tried to change the account, the configuation manager came back with an error message (WMI provider error [call to WMI provider error returned error code: 0x800742a2]).

Only after I added my local account to the local administrator group, I could change the account. I was also able to start up MSSQLSERVER service.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

I have only seen that WMI message after a server rename and the local SQL accounts were orphaned,

e.g. server\SQLServer2005MSSQLUser$server$MSSQLSERVER

check those accounts out for file permissions (their sids will appear but greyed out.)

(If it works after adding to local admins its definitely apermissions issue)

---------------------------------------------------------------------