sql server service/agent permissions

Question

sql server service/agent permissions

Juanita

SSCertifiable

Points: 5300
More actions
September 14, 2006 at 2:11 pm

#172492

HI. I'm setting up a new sql server installation. I'm reading articles that seem to conflict with one another about permissions. I'm using one account for both the sql server service and sql server agent. I have a user account created,(not using the local system acct). Now, exactly what permissions should this account have on the computer(server) itself and what permissions should be set inside the sql server security through enterprise manager.
If there is an article(s) that can answer all this pelase point me to it.
Thanks so much,
Juanita

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

My understanding is that if you change the user accounts for the services via the services applet it will cause you problems. If you however change the user accounts in EM, Enterprise manager take care of all of the necessary user rights changes to directories and Registry entries...

If you choose to do it in the services applet anyhow, here are the steps from MS to fix the security issues that will arise...

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283811

To help us help you read this[/url]For better help with performance problems please read this[/url]

Juanita SSCertifiable Points: 5300 More actions · Answer 2

Thank you. Does the user have to be set up in sql security as a login and if so, what type of server roles should it have? And should this user be made a member of the local administrators group on the computer?

Thanks,

Juanita

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Per the article Link I gave you in my last post...

Miscellaneous steps

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

239759 (http://support.microsoft.com/kb/239759/) Error 5177 may be raised when creating databases

If the account that the MSSQLServer service is going to start with is one of the following two accounts, you must add the startup account for the MSSQLServer and the SQLServerAgent services, or both, to the SQL Server sysadmin)role, and grant the [Domain\NTaccount] user a logon to SQL Server.

•	Not a member of the computer's Local Administrators Group.
•	The BUILTIN\Administrators SQL Server login has been removed.

For example:

EXEC sp_grantlogin [Example\test]

Then, add that account to the sysadmin role:

EXEC sp_addsrvrolemember @loginame = [Example\test]    , @rolename =  'sysadmin'

If you are using SQL Server together with either full-text search or with clustering, changing the SQL Server startup accounts by using anything other than SEM may cause several problems.

If you experience problems with either full-text search or clustering, see the "References" section of this article for more information.

If you are using Kerberos Security Support Provider Interface (SSPI) authentication in a SQL Server 2000 and Microsoft Windows 2000 environment, you must drop your old service principal name (SPN), and then create a new one with the new account information. See the "Security Account Delegation" topic in SQL Server 2000 Books Online for more information about how to use SETSPN to do this.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Juanita SSCertifiable Points: 5300 More actions · Answer 4

HI.. sorry.. i see it now.. missed it at the bottom.

Thanks !

Juanita

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

NP, of course the easiest way to make sure all of this happens is just to change the user in EM.

HTH,

Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]