SQL service agent Account

Question

SQL service agent Account

sqlfriend

SSC Guru

Points: 52769
More actions
April 4, 2011 at 3:54 pm

#389197

How can I find out my sql server agent service account is a sysadmin?
According to sql server bookonline, the service account has to be a sysadmin.
Thanks

Viewing 15 posts - 1 through 15 (of 56 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Do you know the name of your service account?

Check under logins under the security folder. Open the account that you are using as a service account and check the server roles for the account.

You will be doing that from SSMS.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thanks, I know the service account name, but it is not listed in security login list.

We are using sql server 2008 on windows 2008

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

is the service account a domain account that you (Or the network group) created? If so, then add that account under logins and assign the appropriate permissions.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

It's a domain account.

So the installation doesn't add the account automatically to the login list?

Is this a manual step that I have to do after installation?

How come all this time sql server agent is running successfully without the sysadmin rights?

I'm confused.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Is that service account a member of the local admin group in windows (not domain but the machine on which SQL Server is installed)?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

No, it's not in local admin group.

I don't see 'problems' (not sure if it's a problem) like this in our SQL 2005 box.

But I checked in two our SQL 2008, it is like this.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Is the account in a domain group that you have already added with access to the sql server logins?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 8

If you are the sysadmin

If you can login with the sa account

Then

Try SELECT IS_SRVROLEMEMBER('sysadmin');

If returns 1 then Yeah that is an sysadmin account.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

Thanks, I look through the logins in security in SSMS, I don't see it is in any existing group.

SQLDCH SSChampion Points: 11601 More actions · Answer 10

For SQL Server 2008 there will be a group under 'Logins' called 'NT SERVICE\SQLSERVERAGENT'. I haven't figured out how to view that group on the Windows box yet (it's on the list for reading) but that group should be in the 'sysadmin' server role.

That group gets created during the installation of SQL Server. If you've changed the owner of the SQL Server Agent account, make sure you do it using the 'SQL Server Configuration Manager'. You could double check by changing the owner from the existing account to 'Local System', then back to your domain account (provided you can take a little bit of downtime for the agent).

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

sqlfriend SSC Guru Points: 52769 More actions · Answer 11

We do have a group called NT SERVICE\SQLSERVERAGENT.

I have the same question how I can see the member of the group?

We never changed the service account, and when install we give it that domain account for service account.

So my other question is: is it a manual step to add the domain account to the login in security in SSMS?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

@SQLDCH Thanks for clarifying that one.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

here is a query for you to see server roles.

select sp.name as UserAccount ,suser_name(rm.role_principal_id) as RoleName

from sys.server_role_members RM

Inner Join sys.server_principals SP

On RM.member_principal_id = SP.principal_id

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52769 More actions · Answer 14

I got really confused about the service account security of 2008.

I posted a couple of questions, it seems no one really answers that.

Please help.