Lock tabels

Question

Lock tabels

Krasavita

SSC-Insane

Points: 23138
More actions
January 15, 2013 at 9:44 am

#99798

Hello,
Can I put tabels in read only mode?
Thank you
Julia

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

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)

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Krasavita

SSC-Insane

Points: 23138

January 15, 2013 at 11:02 am

#1577439

Can you give me an example please

Lowell SSC Guru Points: 323504 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

Krasavita

SSC-Insane

Points: 23138

January 15, 2013 at 12:38 pm

#1577478

Thank you,but how would you add this to table?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

Krasavita SSC-Insane Points: 23138 More actions · Answer 6

Thank you,How you move the table into a separate filegroup?

Lowell SSC Guru Points: 323504 More actions · Answer 7

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Krasavita SSC-Insane Points: 23138 More actions · Answer 8

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

Lowell SSC Guru Points: 323504 More actions · Answer 9

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!