SQL Data Folder access and permissions
-
I need to upgrade an SQL based application, which should in itself be fairly simple. However, part of the required planning is to verify permissions on the MSSQL/Data folder, to ensure that you understand how SQL is accessing this folder.
I'm fairly good at learning through Google, but here I'm getting frustratingly stuck, because almost all the results to my search terms are answering a slightly different question. So I joined this forum. There's quite a lot of advice out there (generally, through Google) as to what to do at the folder end:
For default instances: add the MSSQLSERVER user (with full permissions)
For a named instance: add the MSSQL$[nameOfInstance]$ user etc
I also administer a different (larger) SQL database on another server running SQL Server 2012 (ignore this, just background, this is not currently being upgraded), and on that 'Data' folder, permissions are very clear and traditional, through MSSQL$[nameOfInstance]$. However, in the case of this application being upgraded (that's currently using SQL Server 2008) I CAN'T SEE ANY SUCH USER in the Windows folder permissions.
So the angle I'm coming at this from is NOT what permissions should I put on the folder BUT RATHER
From the SQL Server end (using SSMS or SSCM) how do I find out what account it is attempting to use to access the database?
Must it be 'MSSQLSERVER'? If so, how come this database and its application is working fine, given that there is no such permission on the data folder? Or could it be hidden somehow? I want to get a clear understanding of this before I proceed with the application upgrade, because some existing permissions may not be correctly detected, and I need the confidence to put the basic permissions back together if I have to.
-
Just to add that I have also been looking on SSMS, 'server properties', permissions. Is the answer here? Can SQL Server use any one of a number of accounts to access the database perhaps, provided those accounts have been granted a particular permission? If so, which permission?
-
I mentioned before that the application upgrade instructions tell you to check for SQLServerMSSQLUser$computername$MSSQLSERVER in the permissions granted against the MSSQL\Data folder and they weren't there (but it is still working). Administrator permissions are applied to the folder, but I'm not sure through what route they would help MSSQL Server access the data.
It may be quite old, but I think the beginnings of an answer MAY be found here, in what Rick Byham says at this link here.
If anyone could provide clarity re my original question, and whether this is particularly relevant, that would be helpful.
-
what you are looking for is the logon account being used under services:
(Control Panel >>Administrative Tools>>Services)
here's a screenshot with four different SQL instances, and you can see three different accounts being used for the four services for SQL
(name@domain and domainame are two different ways of listing the same account.
so in my case, if i was moving all the backups/and data and log folders to a new X:\ drive, for example, i would want to make sure that the specific account being used had access to the new share, or i would want to select a different account, or grant permissions for it to the account currently used by the service.
.
because that account would be creating and deleteing files, i tend to give it full control to the folder in question.
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply