April 15, 2010 at 7:49 am
I wasn't sure of the answers - I did not look them up. instead, I thought I'd just try each one;
sp_changedbowner 'tim'
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
ALTER DATABASE [model] ADD FILE ( NAME = N'modeltest',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modeltest.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
Msg 5013, Level 16, State 1, Line 2
The master and model databases cannot have files added to them. ALTER DATABASE was aborted.
ALTER DATABASE model COLLATE SQL_Latin1_General_CP1_CS_AS
Msg 3708, Level 16, State 5, Line 1
Cannot alter the database 'model' because it is a system database.
drop user guest
Msg 15539, Level 16, State 1, Line 1
User 'guest' cannot be dropped, it can only be disabled. The user is already disabled in the current database.
EXEC sp_dboption "model", "offline", "TRUE";
Msg 5058, Level 16, State 6, Line 1
Option 'OFFLINE' cannot be set in database 'model'.
sp_dboption command failed.
alter database model set read_only
Command(s) completed successfully.
The only case that seemed to work was the read_only.
Hmmmmmmmm !
Tim White
April 15, 2010 at 8:01 am
All answers except last one READ-Only is wrong. You can set Model read only in both 2005 and 2008.
USE [master]
GO
ALTER DATABASE [model] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [model] SET READ_ONLY
GO
Command(s) completed successfully.
SQL DBA.
April 15, 2010 at 8:14 am
ARGGGHHHH ! I can't believe I read the question wrong. I missed the "NOT". Just got in a hurry reading. Slow down Tim......
Tim White
April 15, 2010 at 8:39 am
This is a good question, thank you Ron. I think that though model can be set to read-only, perhaps this action is simply ill-advised, that is all. What I mean is that in the database engine team they know that if one does that then something else might stop working, and therefore, they posted it as one of the restrictions, but forgot to actually raise error when someone tries to do it.
On the other hand, this behavior could very well be by design. The restriction stating "Thou shalt not set the model database to read-only" is there, and it is probably good enough for people to simply comply with it. However, there might be some rare occasions when they do need it to be in this state (for example when troubleshooting something). If this is the case then the restriction on one hand and the actual ability to still have it done is good enough for me.
Oleg
April 15, 2010 at 9:30 am
One suggestion for Question askers... try it before you ask. Now, I don't generally knock question contributors because I do not take the time to contribute myself, so I won't. READ_ONLY is possible.
April 15, 2010 at 10:09 am
jcrawf02 (4/15/2010)
croberts 36762 (4/15/2010)
As already noted, the model database can be set to read-only. Before posting a question, how about actually trying it out?lol and if it didn't work, you'd be complaining that the answer was wrong because BOL also says it CAN. I think this was a fair question, and informative.
I agree that the question is both fair and informative. Since the question is based on the list of settings at the end of the document, I would say it is better to have the document corrected by MS.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 15, 2010 at 10:25 am
Though you can set model to READ_ONLY. However, when you create a new database with READ-ONLY set on the Model DB - the setting does not propagate to the new database. That would tell me that though you can set the Model DB to READ_ONLY, the setting does not do as one might expect.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 15, 2010 at 11:23 am
Interesting question.
Also thanks, Jason, for the extra info on new databases not inheriting the READ_ONLY setting. I would not have expected this behavior.
--
Kevin.
April 15, 2010 at 11:43 am
TempDB is created from model right? So if you were able to set the model to read only and it actually carried through, what would happen when you restarted the server? That could be bad. I guess I'm glad it doesn't carry into new databases. Come to think of it, who would want a brand new empty read-only database anyway? I guess if you had some default tables set up, but would you ever need more than one copy of the database since they would all be exactly the same? Maybe as a security measure or a sanity check to remind you to do some standard-post-database-processing, but I think I'm digging for a reason now.
Chad
April 15, 2010 at 11:44 am
KevinC. (4/15/2010)
Interesting question.Also thanks, Jason, for the extra info on new databases not inheriting the READ_ONLY setting. I would not have expected this behavior.
--
Kevin.
I had to try it since the setting could be set. I wanted to see if a READ_ONLY setting would be enforced in a new database. I expected that it would and thus cause some problems (say you run a script to create your database and then perform the necessary database settings, add files, or create objects) and then you run into errors because the database would have been READ_ONLY. Alas, my suspicion was wrong and the setting does not get enforced.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 15, 2010 at 11:46 am
Chad Crawford (4/15/2010)
TempDB is created from model right? So if you were able to set the model to read only and it actually carried through, what would happen when you restarted the server? That could be bad. I guess I'm glad it doesn't carry into new databases. Come to think of it, who would want a brand new empty read-only database anyway? I guess if you had some default tables set up, but would you ever need more than one copy of the database since they would all be exactly the same? Maybe as a security measure or a sanity check to remind you to do some standard-post-database-processing, but I think I'm digging for a reason now.Chad
Correct on tempdb - another reason to not have model as read_only - or to have that setting propagate. I can't think of a good reason to have a brand new database created as READ_ONLY without reaching.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 15, 2010 at 4:05 pm
Never trust 100% on BOLs. Take them as a reference, and start trying by yourself. That's my advise.
Anyway, the question, good one, leads us all to think deeper and realize about things that we never tried.
Nice. 😉
April 16, 2010 at 7:04 am
This got me learning about the model database. That's a good thing.
Thanks.
April 17, 2010 at 7:00 am
Hi,
I can able set model database to read_only by using query or database properties -->
option-->state--Database read only property to true and also when we create a new database by default it will be read only.the new datase inherit that property. i even tested it long back ago and i refreshed by this question.the newly created database with readonly. we cannot create table or other modifications are not possible.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
April 17, 2010 at 9:03 am
This is a beautiful question. It's a perfect illustration of the fact that you don't know something for certain until you've tried it yourself. I fell into the same trap as did bitbucket (believed what I read in BoL, something I maybe do too often) and ticked all the boxes, so I got my point for getting it wrong :blink:.
Tom
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply