Attached database showing read-only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Attached database showing read-only

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-02-27 : 07:54:43

I attached my database to SQL 2005 but it's showing up as read-only for some reason. I've attached this before just fine in another instance of the Vista OS. Why would it attach differently this time compared to all other times and show read only?

The .mdb and .ldf files are not read only if you right-click and check out their properties.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-27 : 22:40:43

Can you set it to read/write?

DennisMenace
Starting Member

4 Posts

Posted - 2008-02-28 : 02:27:30

I imagine setting to read write would be a simple as
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_WRITE;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-02-28 : 09:36:36

>>>Can you set it to read/write?
I don't see how, not even in the properties of the DB in SQL 2005.

Dennis thanks for the script, I'd have to look it up so didn't know you can just do that. What I ended up doing is just scripting the Databasea (right click - script to new query window) and then took out the READ-ONLY portion of the script that altered the DB to be read only and then ran the script and it created it with no problems.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-01 : 22:25:58

>> I don't see how, not even in the properties of the DB in SQL 2005.

In db properties -> options -> state.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-02 : 08:59:33

I have noticed every restore put its to Readonly and you then change it i thought this must be new in SQL 2005.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-02 : 15:23:55

Not really, ensure source db is not in read only status.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-02 : 18:23:10

The database is not in read mode when you do a restore it will put it to read only automatically.

Is this not happening to you. I done probrably 10-15 restores since SQL 2005 from backups and happens on every one.

Interesting?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-02 : 19:12:20

i have not seen this ever. restoring from backup puts db in read/write if the backup source was read/write.

elsasoft.org

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-02 : 20:00:03

>> The database is not in read mode when you do a restore

Have to check SOURCE db, not the db you RESTORED to.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-03 : 09:56:40

I wonder if the bak was in read only /write mode then like you suggest next time i restore i take a look at it.

Thanks i just thought it was new .

nmoanta
Starting Member

1 Post

Posted - 2008-03-14 : 02:19:44

Right click database, chose Options from left side menu and set Database Read-Only field to False. A confirmation dialog appears, click Yes. You will notice database doesn't appear gray anymore and doesnt't have Read-Only attribute next to it in Object Explorer

Nick M.

CIvory
Starting Member

1 Post

Posted - 2008-04-10 : 11:06:23

The issue was with the db owner for me. I could not simply change the owner. The server would time out if I even right clicked the database in SQL Management Studio. I used the steps below to correct the issue.

Stop the SQL Server

Move the Mdf/Ldf Files

Start the SQL Server

Delete the database

Attached the database with the correct database owner

Now the database is no longer read only

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources