August 20, 2009 at 12:37 pm
I have SQL 2005 Express on one machine and the Enterprise version of SQL 2005 on another. A coworker sent me a detached MDF file without the accompanying LDF. When I tried to attach it using Express it kept telling me that the log file can't be built when the main file is read-only, but the Enteprise version had no trouble building the log file. Same file.
Anyone ever seen this?
August 20, 2009 at 1:46 pm
Haven't seen it myself, but I am wondering how large the mdf file is? Could it be an issue where the data file is too large for Express?
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
August 20, 2009 at 2:05 pm
Does the account that SQL Express is running under have write permissions to the mdf file? Is it on read-only storage?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2009 at 2:23 pm
The user running the SQLExpress service was Network Service. I couldn't figure out how to grant Network Service the rights to that file specifically, but it must be part of Users because when I granted Users Full Control, SQLExpress was able to attach it (so to Jeffrey's point, I guess it wasn't too big! It was only like 60Megs.). Giving Users Write access wasn't enough, it needed Full Control.
But it worked.
The user running the SQL 2005 Enterprise service on the other machine was Local System. Local System must have more privileges than Network Service. Who knows.
Thanks for this callout.
August 20, 2009 at 2:29 pm
Local system is essentially local administrator. Full permissions on the local machine. Network service is a resticted account that has enough permissions to communicate over the network, run as a service and a couple more things.
I would suggest that you revoke the permissions you gave SQLExpress and either change the service account to local system (ok for a local dev instance on a workstation, not recommended for production servers) or create a specific account for SQL and grant it the permissions it needs. It does need full control of directories where data and log files are and full control of those files
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply