Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Lock tabels Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 9:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
Hello,

Can I put tabels in read only mode?

Thank you

Julia
Post #1407343
Posted Tuesday, January 15, 2013 10:55 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:24 PM
Points: 5,847, Visits: 12,580
not directly, but you could move them to a separate filegroup and make that read only, or do it via permissions (only select granted or only access via stored procs)

---------------------------------------------------------------------

Post #1407380
Posted Tuesday, January 15, 2013 11:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
Can you give me an example please
Post #1407385
Posted Tuesday, January 15, 2013 11:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 12,744, Visits: 31,065
here's something i put together for a different post that was talking about setting a table to read only:

this is not copy/paste/execute code, but rather, amodel to use to modify and step through to comprehend what is going on.


USE [master]
GO
ALTER DATABASE SandBox ADD FILEGROUP onlyLookups
GO
ALTER DATABASE [SandBox] ADD FILE ( NAME = N'SandBox_Lookups',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SandBox_Lookups.ndf' ,
SIZE = 2048KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [OnlyLookups]
GO
USE [SandBox]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'OnlyLookups'
if(@readonly=0)
ALTER DATABASE [SandBox] MODIFY FILEGROUP [OnlyLookups] READONLY
GO
USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'OnlyLookups'
if(@readonly=0)
ALTER DATABASE [SandBox] MODIFY FILEGROUP [OnlyLookups] READONLY
GO

--are we readwriteable or not?
USE [SandBox]
GO

--#################################################################################################
--toggle read only, add table, put it back
--#################################################################################################
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'OnlyLookups'
if(@readonly=1)
ALTER DATABASE [SandBox] MODIFY FILEGROUP [OnlyLookups] READWRITE
GO
--switch db context so we can alter
USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'OnlyLookups'
if(@readonly=1)
ALTER DATABASE [SandBox] MODIFY FILEGROUP [OnlyLookups] READWRITE
GO
--#################################################################################################
--add table
--#################################################################################################
USE [SandBox]
GO
CREATE TABLE [dbo].[CITYDATA] (
[CITYNAME] VARCHAR(28) NULL,
[COUNTYNAME] VARCHAR(64) NULL,
[STATE] VARCHAR(2) NULL,
[STATEFIPS] VARCHAR(2) NULL,
[COUNTYFIPS] VARCHAR(3) NULL,
[ZIPCODE] VARCHAR(5) NULL,
[STATETBLKEY] INT NULL,
[COUNTYTBLKEY] INT NULL,
[CBSA] AS ([STATEFIPS]+[COUNTYFIPS]))
ON onlyLookups

--#################################################################################################
--toggle read only back again
--#################################################################################################
USE [SandBox]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'OnlyLookups'
if(@readonly=0)
ALTER DATABASE [SandBox] MODIFY FILEGROUP [OnlyLookups] READONLY
GO
USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'OnlyLookups'
if(@readonly=0)
ALTER DATABASE [SandBox] MODIFY FILEGROUP [OnlyLookups] READONLY
GO



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1407389
Posted Tuesday, January 15, 2013 12:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
Thank you,but how would you add this to table?
Post #1407431
Posted Tuesday, January 15, 2013 12:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
Other way around. This isn't something you add to a table. Rather you move the table into a separate filegroup and mark the filegroup read only (tables can't be read only, only filegroups or databases)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1407439
Posted Tuesday, January 15, 2013 2:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
Thank you,How you move the table into a separate filegroup?
Post #1407498
Posted Tuesday, January 15, 2013 2:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 12,744, Visits: 31,065
Krasavita (1/15/2013)
Thank you,How you move the table into a separate filegroup?


peek at the code i posted, which creates a new table in the filegroup.

for an existing table, you can Use create index ... with drop_existing for the clustered index and specify the desired filegroup for the place that the index must be created on. , which effectively "moves" the table to the new filegroup.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1407499
Posted Wednesday, January 16, 2013 10:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
When I tried to create clustered index as you said and assign to read only file group,I get an error message:that can't save because this file group is readonly
Post #1407974
Posted Wednesday, January 16, 2013 10:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 12,744, Visits: 31,065
Krasavita (1/16/2013)
When I tried to create clustered index as you said and assign to read only file group,I get an error message:that can't save because this file group is readonly


again, look at the code i posted.

i toggle the group to read-write, add the table/index, and then toggle it back to read only.

you will have to do the same,.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1407978
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse