I am having some issues with SQL Express not being able to create a database because the SQL Express account doesn't have permission. During the install I have the user select the local system account. Even then it doesn't work. If I would go into the folder with the database and give the standard users full control the database will attach.
Is there a way to handle this? I have one install doing all of the work so the user really can't interact with the file system during the install. I am looking at Xcacls as a possible solution but is there something I can do in SQL to fix this?
From BOL under Securing Data and Log Files:
Scenario
The following scenario demonstrates the permissions that are set when a database is created, and modified when the database is detached, and attached.
User1, a member of the dbcreator fixed server role, creates the database Sales that has files e:\Data\Sales.mdf and f:\Log\Sales.ldf. At the time the database is created, the MSSQLSERVER service account is SQLServiceAccount2, a local account. Full Control permissions on the database and log files are granted to SQLServiceAccount2 and members of the Windows Administrators group.
A decision is made to move the Sales database to another instance of SQL Server on the same server. Admin3, a member of the sysadmin fixed server role, detaches the database. The Database Engine sets the permissions on the Sales.mdf and Sales.ldf files so that only the Admin3 account has permissions to access the files.
Admin3 connects to the other instance of SQL Server, SalesServer. The MSSQLSERVER service account for the SalesServer instance is SQLSalesServiceAccount. Admin3, a SQL Server administrator on this instance, attaches the Sales database. Full Control permissions are granted to SQLSalesServiceAccount and members of the
xcalcs may be the way to go here.
hth,
-Steven Gott
S/DET
SQL Server
|||I don't see how the BOL entry answers the question. Unless I am missing something the entry has more to do with the actual database permissions as opposed to the permissions to create the log file during the attach process. If I give everyone access to the folder that contains the MDF file I am able to attach it. So that would tell me that SQL Server itself doesn't have permission to the folder.
I tried this on a few other machines and during the install I make sure the local system account is used for the SQL server and it works. I am not sure why the first machine is having such issues.
|||Evan,
The SQL Service account should always be able to create databases and the only reason I can think of that would cause a database to fail to attach is that the ACLs on the database file itself is preventing the attach.
On the machine where this repros what is the service account SQL Express is running under?
What is the error message?
Are you executing sp_attach_db or calling create database for attach?
thanks,
-Steven Gott
S/DET
SQL Server
|||I talked to the guy with the messed up computer and he said he did go into the registry and delete some of those entries so I have no clue what that could mess up.
The SQL is running under the local system account. I know when I first got to the machine I changed the log on for the service to local system checkbox and it wouldn't start. I wonder if those are connected.
|||Unfortunately it seems from your description (manually deleted registry entries) that it may b a non-supported configuration and most likely we will not be able to be of much help as it will be really hard for us to find the root cause of the problem under these circumstances.I would suggest contacting MS support.
I would also like to highly recommend against arbitrarily making manual modifications on SQL Server resources (such as registry keys, ACLs, files, etc.) unless it is documented and/or you know how it will affect SQL Server. If you still need to make such modification I strongly suggest making a backup of any important information you may have on the system and document the modification you are making in order to revert back to a well-known state in case the change doesn’t work as you expected
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
No comments:
Post a Comment