April 4, 2011 at 5:12 pm
I run the query, and I got the below, I removed individual persons account from the list.
I cannot see our service account in it.
The account is not in the BBS_SQLDBA group.
NT AUTHORITY\SYSTEMsysadmin
NT SERVICE\MSSQLSERVERsysadmin
NT SERVICE\SQLSERVERAGENTsysadmin
SEATTLESCHOOLS\BBS_SQLDBAsysadmin
April 4, 2011 at 5:16 pm
annasql (4/4/2011)
I run the query, and I got the below, I removed individual persons account from the list.I cannot see our service account in it.
The account is not in the BBS_SQLDBA group.
NT AUTHORITY\SYSTEMsysadmin
NT SERVICE\MSSQLSERVERsysadmin
NT SERVICE\SQLSERVERAGENTsysadmin
SEATTLESCHOOLS\BBS_SQLDBAsysadmin
as SQLDCH explained - it is being piped through the Local Group called NT Service\SQLSEVERAGENT. If you look under the local groups for the Windows Machine, you will see that group there. That group has an SID attached to it that relates back to AD and your "service account". This is done during SQL 2008 installation. You won't be able to see your service account there either - but since the group has sysadmin permissions - no need to worry.
Not sure where to find reference material showing how to display that service account in the local group.
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
April 4, 2011 at 5:17 pm
I see another person has the same issue:
But it seems no answer either.
The last statement in this link, says SQL service agent doesn't need to be a sysadmin, but sql server book online says it has to be.
So what is the right one?
Thanks
April 4, 2011 at 5:20 pm
Thanks, but the problem is I cannot see a group in our windows local group called, NT Service\SQLSEVERAGENT.
You can try it too, it's not there.
Thanks
April 4, 2011 at 5:34 pm
Let's try and approach this from a different perspective: is there a specific problem related to the agent in the 'sysadmin' role that you're trying to solve?
If you specified an account for your SQL Server Agent service during installation, or if you used the 'SQL Server Configuration Manager' to set the account owner, that account will be placed in the 'NT SERVICE\SQLSERVERAGENT' group. As I stated, and you confirmed, that group doesn't appear on the Windows server (I'll figure out why later).
I would go with what Books Online recommends for role membership for that account.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
April 4, 2011 at 5:40 pm
Here's a couple of links that may help:
http://support.microsoft.com/kb/955813
http://msdn.microsoft.com/en-us/library/ms143504(SQL.100).aspx
I just double checked my servers, in the local Windows groups there are the groups that I was used to seeing from the SQL Server 2005 days:
SQLServerMSSQLUser$ComputerName$MSSQLSERVER
SQLServerSQLAgentUser$ComputerName$MSSQLSERVER
If you look inside those groups, you'll see the only member is 'NT SERVICE\MSSQLSERVER' and 'NT SERVICE\SQLSERVERAGENT', respectively. Jason explained that those groups contain the necessary SID(s) to map back to Active Directory, which I'm now guessing is a way to abstract those accounts, as they are in the 'sysadmin' role.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
April 4, 2011 at 5:41 pm
So far I don't find any errors for sql agent service account.
But I want to setup a good practice for service account.
If books onlins said it has to be a sysadmin, I think it should setup like that way.
For windows group, I will make it clear:
I can see a group in windows local group called
SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER,
it includes the account NT Service\SQLServerAgent as a member. But I cannot see the domain service accoun tin this group. And what relationship does the domain account have to do with this NT service|Sqlserveragent, I cannot see any connections with them. This is also the case in the other persons link I provided, he wrote the same question , see the highlighted in green in the link.
Thanks
April 4, 2011 at 5:43 pm
Thanks, not sure what this means:
"Jason explained that those groups contain the necessary SID(s) to map back to Active Directory, which I'm now guessing is a way to abstract those accounts, as they are in the 'sysadmin' role. "
April 4, 2011 at 7:53 pm
annasql (4/4/2011)
Thanks, but the problem is I cannot see a group in our windows local group called, NT Service\SQLSEVERAGENT.You can try it too, it's not there.
Thanks
That is interesting - I see it there and I see the AD generated guid generated for the account attached to it.
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
April 4, 2011 at 7:57 pm
annasql (4/4/2011)
Thanks, not sure what this means:"Jason explained that those groups contain the necessary SID(s) to map back to Active Directory, which I'm now guessing is a way to abstract those accounts, as they are in the 'sysadmin' role. "
An SID is a System Identifier that is typically a guid. AD generates an SID for every object held in the domain from computers to groups to service accounts. If you know the SID of the account in question you can compare it to the SID you see in the LOCAL group we have already discussed.
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
April 4, 2011 at 8:07 pm
I can see the nt service account and its sid in the group called SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER,
Did you see a separate group called NT Service\SQLServerAgent ? And what's member?
See below:
I can see a group in windows local group called
SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER,
it includes the account NT Service\SQLServerAgent as a member. But I cannot see the domain service accoun tin this group. And what relationship does the domain account have to do with this NT service|Sqlserveragent, I cannot see any connections with them. This is also the case in the other persons link I provided, he wrote the same question , see the highlighted in green in the link.
April 5, 2011 at 12:11 pm
No one seems to answer this question.
But this is really important for security.
Maybe this question should all start with:
Does the sql server agent account have to be a sysadmin?
Thanks
April 5, 2011 at 12:14 pm
annasql (4/5/2011)
No one seems to answer this question.But this is really important for security.
Maybe this question should all start with:
Does the sql server agent account have to be a sysadmin?
Thanks
Yes
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
April 5, 2011 at 12:41 pm
And Is it a manual process after installation to add the service account in my case is a windows domain account to the windows local group called:SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER
April 5, 2011 at 1:03 pm
annasql (4/5/2011)
And Is it a manual process after installation to add the service account in my case is a windows domain account to the windows local group called:SQLServerSQLAgentUser$Mycomputername$MSSQLSERVER
@SQLDCH already answered that. Use the SQL Configuration Manager and it will add the accounts to the necessary groups for you.
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
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply