June 6, 2012 at 10:36 am
A question I have not got a clear answer:
When installing SQL server 2008 or 2008 r2, we choose to use a domain account as the service account, for example I will call it mydomain\mysqlsvc. I didn't specifically grant the account as sysadmin.
So my question is:
Is the SQL service account mydoman\mysqlsvc by default a sysadmin of SQL server or not?
Thanks
June 6, 2012 at 11:29 am
When you assign a domain account to the service through SQL Server Configuration Manager or during installation, it grants the user the proper permissions by assigning it to a "virtual user" within SQL Server. The user itself does not have to have an SQL Server login.
Jared
CE - Microsoft
June 6, 2012 at 11:42 am
SQLKnowItAll (6/6/2012)
it grants the user the proper permissions by assigning it to a "virtual user" within SQL Server. The user itself does not have to have an SQL Server login.
Thanks, but not sure what above mean?
So it is not by default a sysadmin, correct?
June 6, 2012 at 11:46 am
sqlfriends (6/6/2012)
SQLKnowItAll (6/6/2012)
it grants the user the proper permissions by assigning it to a "virtual user" within SQL Server. The user itself does not have to have an SQL Server login.Thanks, but not sure what above mean?
So it is not by default a sysadmin, correct?
The domain user is not, the service account is. The domain user does not have to be a valid sql server login.
Jared
CE - Microsoft
June 6, 2012 at 12:55 pm
It is going to depend on how you installed SQL Server 2008 or 2008 R2.
To explain - we look back at how 2005 managed access to SQL Server for the service account. When using a domain account, the domain account is added to a local windows group (or domain group for a cluster). That local windows group was added to SQL Server as a login and granted sysadmin rights. Because the domain account is a member of that group - it had sysadmin rights. The domain account was able to login as itself to SQL Server...
In 2008 and above (when installed on Windows Server 2008 and above), you have the option of installing using service SID's - which is the default installation method. When choosing this method, the domain account is setup to run the services and new principals are created in Windows called:
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT
These new principals are then added to SQL Server as logins and granted sysadmin rights to the system. Now, the service account has the rights necessary to run the service, and this service SID has the necessary rights it needs in SQL Server.
The service account (domain account) no longer has direct sysadmin rights on the database system. You cannot log into the server with the service account and get access to SQL Server, nor can you impersonate the service account and gain access either - unless you specifically add the service account as a login with the necessary rights.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2012 at 3:19 pm
Thanks,
Sorry still confusing:
Quote from SQLKnowitall:
The domain user is not, the service account is. The domain user does not have to be a valid sql server login.
Quote from Jeff:
The service account (domain account) no longer has direct sysadmin rights on the database system
June 6, 2012 at 3:25 pm
sqlfriends (6/6/2012)
Thanks,Sorry still confusing:
Quote from SQLKnowitall:
The domain user is not, the service account is. The domain user does not have to be a valid sql server login.
Quote from Jeff:
The service account (domain account) no longer has direct sysadmin rights on the database system
Ok. think of it this way. Your domain user is domain\sqlfriends
You set this up as the service account login, but do not add the domain user to sql server.
domain\sqlfriends cannot access sql server
NT SERVICE\MSSQL SERVER is running its service in windows using domain\sqlfriends login, but is a virtual user and does not require the domain user to also be its own user in sql server. This allows the server services to run under accounts that do not necessarily have rights to log into sql server.
Jared
CE - Microsoft
June 6, 2012 at 3:26 pm
We do use default installation.
also:
You cannot log into the server with the service account and get access to SQL Server, nor can you impersonate the service account and gain access either - unless you specifically add the service account as a login with the necessary rights.
I think we have to add the sql service account (the domain account) into SQL login and grant specific permissions for databases, for most of the jobs steps are running under SQL agent service account by default.
I don't think here SQL agent service account means NT SERVICE\SQLSERVERAGENT,correct? Otherwise it will have sysadmin rights for all the jobs.
June 6, 2012 at 5:31 pm
sqlfriends (6/6/2012)
We do use default installation.also:
You cannot log into the server with the service account and get access to SQL Server, nor can you impersonate the service account and gain access either - unless you specifically add the service account as a login with the necessary rights.
I think we have to add the sql service account (the domain account) into SQL login and grant specific permissions for databases, for most of the jobs steps are running under SQL agent service account by default.
I don't think here SQL agent service account means NT SERVICE\SQLSERVERAGENT,correct? Otherwise it will have sysadmin rights for all the jobs.
I'm not sure that you completely understand yet. The jobs on a schedule will always run under the "service account" which is the NT SERVICE\SQLSERVERAGENT no matter which domain account is configured as the login for that service. The jobs do not run under the job owner's account. In your quoted statement from above, it may be better to say this:
You cannot log into the server with the domain user associated with the service account and get access to SQL Server, nor can you impersonate the service account and gain access either - unless you specifically add the domain user associated with the service account as a login with the necessary rights.
Jared
CE - Microsoft
June 6, 2012 at 5:48 pm
I'm not sure that you completely understand yet. The jobs on a schedule will always run under the "service account" which is the NT SERVICE\SQLSERVERAGENT no matter which domain account is configured as the login for that service. The jobs do not run under the job owner's account.
So if the job is running under SQL service account which you mean nt service\SQLserveragent, then it has sysadmin rights, and if we have a job like SSIS package that runs using sql agent service account,because it has sysAdmin role, we don't need to specially grant permission to it.That doesnot sound right.
What we usually do is to add the domain account to sql login, and grant the permission to a specific database, so SSIS can run successfully ( here I don't want to talk proxy account, just sql agent service account).
Thanks,
June 6, 2012 at 8:05 pm
sqlfriends (6/6/2012)
I'm not sure that you completely understand yet. The jobs on a schedule will always run under the "service account" which is the NT SERVICE\SQLSERVERAGENT no matter which domain account is configured as the login for that service. The jobs do not run under the job owner's account.
So if the job is running under SQL service account which you mean nt service\SQLserveragent, then it has sysadmin rights, and if we have a job like SSIS package that runs using sql agent service account,because it has sysAdmin role, we don't need to specially grant permission to it.That doesnot sound right.
What we usually do is to add the domain account to sql login, and grant the permission to a specific database, so SSIS can run successfully ( here I don't want to talk proxy account, just sql agent service account).
Thanks,
Again, I think there is a misunderstanding (from me too). Jobs themselves are run under the service (NT SERVICE\SQLSERVERAGENT) account. The steps themselves can be executed under different accounts. That being said, I can show you that on my machine the steps are not being run by any SQL Server login. Attached below.
Jared
CE - Microsoft
June 6, 2012 at 10:52 pm
see my job in the attachment, it is the same job as yours, it is all executed as the user- the domain user that for sql agent service login. in the example the domain account we use is mydomain\sqlservice29
This is the same on our server either 2008 or 2008R2.
And in fact I checked almost all job historythe very beginning, it all says: executed as the user (the domain account for sql agent service)
June 7, 2012 at 5:51 am
sqlfriends (6/6/2012)
see my job in the attachment, it is the same job as yours, it is all executed as the user- the domain user that for sql agent service login. in the example the domain account we use is mydomain\sqlservice29This is the same on our server either 2008 or 2008R2.
And in fact I checked almost all job historythe very beginning, it all says: executed as the user (the domain account for sql agent service)
Yes, my point is that that domain user DOES NOT have to be an SQL login. It "can" be, but does not have to be.
Let me ask you this... What OS are you running? You will see from this link that Windows Server 2008 R2 uses the virtual accounts that I am talking about. However, Windows Server 2008 (not R2) still uses a NETWORK SERVICE account.
Jared
CE - Microsoft
June 7, 2012 at 8:19 pm
We use both 2008 and 2008 R2 windows system, and I checked on both systems, the sql job is executed as the SQL service agent account ( the domain user).
I guess I will have to study that document you give in the link to understand better
Thanks
May 21, 2013 at 3:49 pm
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply