0

I have an .mdf/.ldf pair, originally created in 2008 R2 Standard, and well under 10GB, with ACLs:

d:\db snapshot\DB_NAME.mdf
    SERVERNAME\SQLServerMSSQLUser$ACCOUNT$MSSQLSERVER:F
    OWNER RIGHTS:F
    BUILTIN\Administrators:F

d:\db snapshot\DB_NAME_log.ldf
    SERVERNAME\SQLServerMSSQLUser$ACCOUNT$MSSQLSERVER:F
    OWNER RIGHTS:F
    BUILTIN\Administrators:F

When I attach the database to an instance of SQL Express 2008 R2, it comes up as read-only. When exactly the same acls and user-accounts and SQLCMD statements are set up with SQL Web 2008 R2, it comes up writable. I looked at MSDN's comparison page but nothing jumped out at me. Why on earth is this happening? Thanks!

David Rubin
  • 153
  • 1
  • 2
  • 7
  • 1
    Check the logs on SQL Server express for the attachment. If something happened then it should be in the logs. – TomTom May 29 '14 at 19:11
  • Is the database state (Read Only, Single User, Suspect) related to the NTFS permissions on the database files? – joeqwerty May 29 '14 at 19:20
  • @joeqwerty That was my theory, but if it were, it doesn't explain why SQL Web and SQL Express exhibit different behavior with exactly the same file permissions. – David Rubin May 29 '14 at 19:23
  • @TomTom There's nothing informative in the logs either, and certainly no errors; just "Starting up database 'DB_NAME'. Is there a verbosity setting I can turn up? – David Rubin May 29 '14 at 19:30
  • Is the database fully online in SQL Express? Check the `state_desc` column of the `sys.databases` view. Perhaps it's still restoring? – Bob Pusateri May 29 '14 at 20:03
  • Also how do you know it's read-only? Are you seeing this in SSMS object explorer? or is it throwing you an error? – Bob Pusateri May 29 '14 at 20:04
  • @BobPusateri I know (don't I?) it's ready-only because it says so in SMSS _and_ `ALTER`ing a table gives an error. – David Rubin May 29 '14 at 20:07
  • Which user accounts are used to run the two SQL Server instances? – Massimo May 29 '14 at 20:09
  • What edition/version of SQL Server was this database originally created in? (Not sure this really makes a difference, just curious) – Bob Pusateri May 29 '14 at 20:14
  • @BobPusateri It was created in 2008 R2 Standard. I updated the question to include that information. And the state_desc column says "ONLINE" for both Express and Web. – David Rubin May 29 '14 at 20:24
  • FYI, there is a difference between a database being in read-only mode, and SQL Server blocking you from modifying it; it could very well be the case that the database is fully online and writable in both instances, but your user account doesn't have write permissions on it in SQL Server Express, while instead it has them in SQL Server Web. – Massimo May 29 '14 at 20:25
  • @Massimo It appears the sqlservr.exe process is running as NETWORKSERVICE for SQL Express, but LOCALSYSTEM for SQL Web. – David Rubin May 29 '14 at 20:39

3 Answers3

5

If SQL Server Web is running as LocalSystem while instead SQL Server Express is running as NetworkService, then it's perfectly correct that SQL Server Express doesn't have write permissions on the database files, because the NetworkService account doesn't have administrative rights (although, based on the ACLs you posted, it shouldn't even have read permissions... but maybe they are inherited from somewhere else).

Try giving Full Control permissions on those files to the NetworkService account, and the issue should be fixed.

Massimo
  • 70,200
  • 57
  • 200
  • 323
2

My best guess is that this is related to permissions for the account that the SQL Server service is executing as. It looks like others have seen this issue before, and if the service account can read the files you're attaching but not write to them, the database will be in a read-only state.

Community
  • 1
Bob Pusateri
  • 121
  • 4
0

This Solved my Attached database READ ONLY problem even after permissions re-granted on database itself and root folders above it! Try This before you go bald more.

This was originally caused by clonezilla. I changed my hard drive from a SSD (OS was on it) to Massive 3 TB hard drive. I rather have slow but spacious HD. After the clone, I renamed the server ( Homer... duh) and Copy and pasted my websites onto a newer hard drive which broke all my D drive permissions by doing so. None of my D drive databases could ever be without the READ ONLY NAG!!!! This baffled me as the D drive Root directory Subfolder had ALL PERMISSIONS RE-GRANTED EXCEPT: MSSQL11$SQLEXPRESS Where did this go?

Obviously, I had a separate directory on my D:\SQLDATABASES\ drive for all of my databases both SQL and MySQL. I had gave all the permissions and then some but still no go! It turn out that I was missing the MSSQL11$SQLEXPRESS

Since I purchased at BestBuy two (3 TB harddrives) My C drive with the OS was a clone, my D drive was stupidly copied and pasted and not cloned which would of fixed this mess.

In Summation, if you have Windows Server 2012 and you tripled checked permissions but are missing MSSQL11$SQLEXPRESS ( you can't even add this in permissions ...does not exists) just cut and paste your database(s) into the default SQL path and Viola crisis averted. I knew this was a permissions issue but it was about to be solved by my sledgehammer! Please let this simple post help someone other than myself.

my default SQL Path is Below: THIS WORKED yeah baby! Paste it in Data directory

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA

Mike Caldera
  • 191
  • 1
  • 3