Solved

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

Posted on 2009-07-06
14
781 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
Comment Utility
run 'sp_helpdb ASSCAP'

Can you copy/paste in the status and compatability level

Cheers
0
 
LVL 18

Author Comment

by:jmoss111
Comment Utility
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
Comment Utility
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
 
LVL 18

Author Comment

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

Expert Comment

by:St3veMax
Comment Utility
Doh. Try taking out the _

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

0
 
LVL 18

Author Comment

by:jmoss111
Comment Utility
correctamundo
0
 
LVL 13

Expert Comment

by:St3veMax
Comment Utility
What error does it give when it fails?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 18

Author Comment

by:jmoss111
Comment Utility
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
Comment Utility
I can attach and detach on the other machine at will no problem, and no space problem
0
 
LVL 13

Expert Comment

by:St3veMax
Comment Utility
>>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
Comment Utility
files not set to read only. i'm dbo...
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 500 total points
Comment Utility
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
Comment Utility
Thanks for hanging with me on this.
0
 
LVL 13

Expert Comment

by:St3veMax
Comment Utility
No Worries :o)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now