Solved

SQL Server 2005 database in Read-only state= True, can't set to False

Posted on 2009-07-06
14
785 Views
Last Modified: 2012-06-27
I have a SQL Server 2005 database moved from one machine to another and when moved Read-only = True; I can't set Read-Only to false via the interface, or by using either of the below:
 ALTER DATABASE ASSCAP SET READ_WRITE,
or
EXEC sp_dboption 'ASSCAP', 'read only', false

Thanks in advance for any assistance.

Jim
0
Comment
Question by:jmoss111
  • 7
  • 7
14 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 24785753
run 'sp_helpdb ASSCAP'

Can you copy/paste in the status and compatability level

Cheers
0
 
LVL 18

Author Comment

by:jmoss111
ID: 24785803
copy/paste wont pull over from rdp:

status  = online, updatability = read_only, useraccess=multi_user, recovery = full
compatibility =90

0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24785832
Sidenote: It will if under 'Local Resources' you click on 'Clipboard'.

Anyway....

What if you try:

ALTER DATABASE ASSCAP SET READ_WRITE WITH ROLLBACK_IMMEDIATE

??

If that fails, run "sp_who2" and see who's in that DB, kick them out and try again. Other suggestions, check the physical files to ensure the account running SQL has full permission to them.

HTH
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 18

Author Comment

by:jmoss111
ID: 24785886
the ALTER  gave me incorrect syntax near ROLLBACK_IMMEDIATE. User was kicked out, my account is dbo
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24785914
Doh. Try taking out the _

File permissions all OK, No-one in the database, including you ?

0
 
LVL 18

Author Comment

by:jmoss111
ID: 24786095
correctamundo
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24786111
What error does it give when it fails?
0
 
LVL 18

Author Comment

by:jmoss111
ID: 24786154
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP_log.ldf". Operating system error 5: "5(Access is denied.)".
File activation failure. The physical file name "D:\SQL2005\ASSCAP_log.ldf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'ASSCAP' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
0
 
LVL 18

Author Comment

by:jmoss111
ID: 24786164
I can attach and detach on the other machine at will no problem, and no space problem
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24786192
>>Msg 5120, Level 16, State 101, Line 1
>>Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".

I'm still thinking physical file security and/or the file is marked as read only.
0
 
LVL 18

Author Comment

by:jmoss111
ID: 24786215
files not set to read only. i'm dbo...
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 500 total points
ID: 24786233
doesent matter if you're dbo - the physical files need to grant NTFS access to the account running SQL Server.

e.g ensure YourDomain\svc_sql has permission to that file.

HTH
0
 
LVL 18

Author Closing Comment

by:jmoss111
ID: 31600182
Thanks for hanging with me on this.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24786361
No Worries :o)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question