?
Solved

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

Posted on 2009-07-06
14
Medium Priority
?
788 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

762 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