February 21, 2012 at 8:36 pm
Comments posted to this topic are about the item Model Database
February 21, 2012 at 8:37 pm
February 21, 2012 at 8:53 pm
A very good question Steve
M&M
February 21, 2012 at 9:30 pm
select @@VERSION
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
Are answer right?
Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)
I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database
I Have Nine Lives You Have One Only
THINK!
February 21, 2012 at 9:43 pm
handkot (2/21/2012)
select @@VERSIONMicrosoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
Are answer right?
Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)
I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database
I agree, tested in R2, model can be set to READ_ONLY, but new DB not READ_ONLY.
Selected "Yes and No" as the answer.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 21, 2012 at 9:44 pm
handkot (2/21/2012)
select @@VERSIONMicrosoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
Are answer right?
Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)
I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database
You are running SQL 2008(10.0.5500.0) not SQL 2008 R2 (10.5.xxxx.0).. And the answer is correct. Just verified it..
I can't think of a case where I would ever do this but its good to know..
CEWII
February 21, 2012 at 9:53 pm
Please see screen shot of the test I did....I'm running R2....
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 21, 2012 at 10:23 pm
A good question to learn something.
But the new db created are not with read only option.
Are we missing something here. 🙂
February 22, 2012 at 12:32 am
Damn you MSDN!
Any documentation that supports the correct answer?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2012 at 1:08 am
Koen Verbeeck (2/22/2012)
Damn you MSDN!Any documentation that supports the correct answer?
I found this: http://msdn.microsoft.com/en-us/library/ms190249.aspx
It states that:
To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.
In the list of options there is READ_ONLY
February 22, 2012 at 1:16 am
I absolutely agree on that!
Elliott Whitlow (2/21/2012)
handkot (2/21/2012)
select @@VERSIONMicrosoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
Are answer right?
Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)
I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database
You are running SQL 2008(10.0.5500.0) not SQL 2008 R2 (10.5.xxxx.0).. And the answer is correct. Just verified it..
I can't think of a case where I would ever do this but its good to know..
CEWII
February 22, 2012 at 1:26 am
Based on a vague recollection, I answered that you can set it on the model database, but it won't affect new databases. I then found I apparently was wrong.
After reading the comments, I decided to check it:
USE tempdb;
SELECT @@version;
go
SELECT name, is_read_only FROM sys.databases WHERE name = 'model';
ALTER DATABASE model SET READ_ONLY WITH NO_WAIT;
SELECT name, is_read_only FROM sys.databases WHERE name = 'model';
go
CREATE DATABASE Test_QotD;
SELECT name, is_read_only FROM sys.databases WHERE name IN ('model', 'Test_QotD');
DROP DATABASE Test_QotD;
go
ALTER DATABASE model SET READ_WRITE WITH NO_WAIT;
go
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
name is_read_only
------------------------------------------- ------------
model 0
name is_read_only
------------------------------------------- ------------
model 1
name is_read_only
------------------------------------------- ------------
model 1
Test_QotD 0
(EDIT: Removed white space from query results for readability)
February 22, 2012 at 1:32 am
After the comments, I think its safe to say:
Yes, model can be set to Read_Only
No, it wont affect new databases created?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 22, 2012 at 1:34 am
Carlo Romagnano (2/22/2012)
Koen Verbeeck (2/22/2012)
Damn you MSDN!Any documentation that supports the correct answer?
I found this: http://msdn.microsoft.com/en-us/library/ms190249.aspx
It states that:
To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.
In the list of options there is READ_ONLY
Close enough 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2012 at 1:48 am
Hugo Kornelis (2/22/2012)
Based on a vague recollection, I answered that you can set it on the model database, but it won't affect new databases. I then found I apparently was wrong.After reading the comments, I decided to check it:
Running your code I get the same results.
However, try this through the SSMS GUID. If I change 'Database Read Only' to TRUE from the Properties and then right-click on 'Databases' to create a New Database, it does as given in the answer.
Two ways to do this with two different results.
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply