Solved

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

Posted on 2009-07-06
14
786 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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