<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Jason Brimhall
»
Model DB
42 posts, Page 1 of 5
1
2
3
4
5
»
»»
Model DB
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
SQLRNNR
Posted Wednesday, June 09, 2010 9:14 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 18,848,
Visits: 12,433
Comments posted to this topic are about the item
Model DB
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935106
UMG Developer
UMG Developer
Posted Wednesday, June 09, 2010 9:16 PM
SSCrazy
Group: General Forum Members
Last Login: Monday, June 10, 2013 1:20 PM
Points: 2,163,
Visits: 2,149
Great question, thanks! Though it would be nice for a little explanation behind the results.
Considering that
BOL
states:
"The following operations cannot be performed on the model database:
•Setting the database or primary filegroup to READ_ONLY."
Which obviously isn't true, you can set it, it just doesn't propagate it to new DBs.
Though the first option for script 4 should be updated to say NewReadOnlyDB instead of TestA. (That is what gave it away for me.)
Post #935107
SQLRNNR
SQLRNNR
Posted Wednesday, June 09, 2010 10:19 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 18,848,
Visits: 12,433
Thanks. True - BOL does state that the MODEL DB cannot be set to ReadOnly, that's why I found this to be an interesting behavior.
Thanks for pointing out the DB name too.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935118
malleswarareddy_m
malleswarareddy_m
Posted Wednesday, June 09, 2010 10:19 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 3:10 AM
Points: 1,867,
Visits: 1,040
hi without the script i set the model database into read only.then i create the new database but i am not able to create the new table because it is read only it gives an error as one of the option mentioned.but with the script i am able to create new table.
but it shows the model database as read only.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #935119
SQLRNNR
SQLRNNR
Posted Wednesday, June 09, 2010 10:22 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 18,848,
Visits: 12,433
malleswarareddy_m (6/9/2010)
hi without the script i set the model database into read only.then i create the new database but i am not able to create the new table because it is read only it gives an error as one of the option mentioned.but with the script i am able to create new table.
but it shows the model database as read only.
When you attempt to create a table, make sure you have switched context to the new database.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935121
SQLRNNR
SQLRNNR
Posted Wednesday, June 09, 2010 10:24 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 18,848,
Visits: 12,433
UMG Developer (6/9/2010)
Though the first option for script 4 should be updated to say NewReadOnlyDB instead of TestA.
Steve, could you update that option? It is the answer option with the error message.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935122
malleswarareddy_m
malleswarareddy_m
Posted Wednesday, June 09, 2010 10:40 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 3:10 AM
Points: 1,867,
Visits: 1,040
I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)
then create new db with wizard. It automatically creates new database with Readonly.
Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #935133
SQLRNNR
SQLRNNR
Posted Wednesday, June 09, 2010 10:48 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 18,848,
Visits: 12,433
malleswarareddy_m (6/9/2010)
I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)
then create new db with wizard. It automatically creates new database with Readonly.
Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.
Interesting.
If I understand correctly, you:
1) Set the Model DB to readonly
2) Use the wizard to create a new database
3) Switch context to the new database
4) execute a create table statement - which fails
What version and SP of SQL server? When I do those steps I see the same results as the script (sql 2005 and 2008).
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935136
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Thursday, June 10, 2010 1:38 AM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:46 AM
Points: 3,157,
Visits: 4,341
Good question.
I found the inconsistency with BOL interesting.
Thanks
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #935202
michael.kaufmann
michael.kaufmann
Posted Thursday, June 10, 2010 2:30 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 28, 2013 1:34 AM
Points: 1,263,
Visits: 1,080
Just verified the finding in SQL Server 2005:
- With MODEL being in READ_ONLY mode, when creating a database using the wizard, it is created read_only. No more changes are possible.
- With MODEL still in READ_ONLY, when creating a database via a SQL script, it is created in read/write mode and tables etc. can be added.
Very interesting behavior--especially given the fact that BOL states one cannot set the MODEL database to READ_ONLY (that's why I did not get my point).
Post #935217
« Prev Topic
|
Next Topic »
42 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
