Click here to monitor SSC
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
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 1958
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
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 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
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
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
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 1958
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 1958
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
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 844
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