SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Permissions on Service Account


Permissions on Service Account

Author
Message
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3433 Visits: 2000
I was under the impression that when you specify the SQL Service Account during install of SQL Server, ALL rights needed for the service account were granted to the account specified as the service account. I thought it was not necessary to add addional rights, such as adding the sevice account to local admin group, But here's what I just ran into...

I insalled SQL 2008 R2-64 bit on a VM. I created a share on the VM and copied a database backup into it. When connencted to the instance via SSMS as "sa" and trying to restore the backup, the restore backup wizard was unable to see any files in the share. When types the name of the backup file, I got an error saying that the database engine could not see the file doe to it not existing or having sufficient rights. Then I logged on to the server via RDP as Administrator (which was added to SQL as a sysadmin)and had the same result, even though Administrator see the the BAK file in the file system.

Next I moved the BAK file to the folder designated as the backup folder during the SQL install. The remote SSMS session (as sa) still could not access the BAK, but the local SSMS session could and was able to restore the DB.

So is it often nessisary to give the SQL service account additional after assigning itas the service account during install or in SQL Configuration Manager?
Vikrant S Patil
Vikrant S Patil
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 343
I think there are instances when SQL Server service account will require access to something that it may not be directly under its purview.

Therefore most DBAs keep life simple by making the service account a member of local administrator's group.



Sachin Nandanwar
Sachin Nandanwar
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1503 Visits: 2633
Under which account is the sql server service running ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18341 Visits: 10039
The installation grants all of the rights necessary for that service account to run SQL Server.

That does not mean that service account has full administrator access to the server, or - in fact - access to all of the folders in the system.

As of 2008 on Windows Server 2008, it gets even tighter and your service account doesn't even have access to SQL Server. The service is run under the service account - and a special service SID is created - and that service SID is granted access.

So - yes, if your service account needs to do anything else then you need to grant the additional privileges.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3433 Visits: 2000
Thanks. Altough you would think that when logging in remotely as "sa" you should see files in the folder designated as the "Backup" location during installation.

In the interests of testing the "minimum rights needed to do the job" philisophy, I will give the "SQLService" account read only rights to the network share I initially copied the BAK to and see if sa can see files there when connected remotely.
Lowell
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68664 Visits: 40900
the hiccup there, as you identified, is when files exist on the network, and not the local disk.

The system account is one of those special accounts that never logs into the domain, so it doesn't get access to any network shares.

If the instance ever needs anything except local disk access, I usually create a new user like SQLAdminMachineName in ActiveDirectory, and give it the network shares it needs access to, and use that as the startup acocunt for the service instead of the system account.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3433 Visits: 2000
In this case, the network share is on the same server as the SQL instance. I use that share to copy items (like BAK files from other SQL instances) to this server. At work we put the service accounts in the local admin group because the application vendor says to, but in case I get a future job at a place that likes to lock down security, but in my self-training I like to figure out the minimum rights needed.
heymiky
heymiky
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1413 Visits: 845
Check out your share level permissions if you are using this to specify your backup location as the most restrictive rights will apply.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search