locking table

Question

locking table

sekannak

Valued Member

Points: 72
More actions
September 5, 2009 at 5:19 am

#214203

Hi,
I have a table - how to lock my table not to INSERT/UPDATE/DELETE plz answer me...
regards,
kannak.....

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

sekannak (9/5/2009)
Hi,
I have a table - how to lock my table not to INSERT/UPDATE/DELETE plz answer me...
regards,
kannak.....

One way that i can think of doing this is put this table in a read only filegroup which will prevent insert/update/delete operation on the table.

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 2

The other way is to create a insert/update/delete trigger that roll backs any modification to the base table, however i still prefer the previous method of putting the table in a read only filegroup..

Pradeep Singh

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 3

Marcin Gol

Hall of Fame

Points: 3314

September 6, 2009 at 12:35 pm

#1049894

maybe you can use deny permission ?

Paul White SSC Guru Points: 150467 More actions · Answer 4

The following script illustrates one way to use a read-only file group.

-- Temporary database

CREATE DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2];

GO

-- Add a file group to hold read-only tables

ALTER DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2]

ADD FILEGROUP [FG_READ_ONLY];

GO

-- Add an NDF file to the filegroup

DECLARE @data_path NVARCHAR(256);

SET @data_path =

(

SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)

FROM master.sys.master_files

WHERE database_id = 1

AND [file_id] = 1

);

EXECUTE

(

'ALTER DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2]

ADD FILE

(

NAME = F_Read_Only,

FILENAME = ''' + @data_path + 'F_Read_Only.NDF'',

SIZE = 64MB,

MAXSIZE = 64MB,

FILEGROWTH = 0MB

)

TO FILEGROUP [FG_READ_ONLY];'

);

-- Switch to the new database

USE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2];

GO

-- Test table (read-write)

CREATE TABLE dbo.ExistingTable

(

col1 INTEGER IDENTITY

CONSTRAINT [PK dbo.ExistingTable col1]

PRIMARY KEY CLUSTERED

WITH (FILLFACTOR = 100)

ON [PRIMARY]

);

GO

-- Add some rows

INSERT dbo.ExistingTable DEFAULT VALUES;

GO

-- Show the data

SELECT *

FROM dbo.ExistingTable;

GO

-- Move the data to the new file group

ALTER TABLE dbo.ExistingTable

DROP CONSTRAINT [PK dbo.ExistingTable col1]

WITH (MOVE TO [FG_READ_ONLY]);

GO

-- Re-create the primary key

ALTER TABLE dbo.ExistingTable

ADD CONSTRAINT [PK dbo.ExistingTable col1]

PRIMARY KEY CLUSTERED (col1)

WITH (FILLFACTOR = 100)

ON [FG_READ_ONLY];

-- Now make the file group read only

ALTER DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2]

MODIFY FILEGROUP [FG_READ_ONLY]

READ_ONLY;

-- Show the data

SELECT *

FROM dbo.ExistingTable;

GO

-- Modifications fail now with the message:

-- (The index ... resides on a read-only filegroup ("FG_READ_ONLY"), which cannot be modified.

INSERT dbo.ExistingTable DEFAULT VALUES;

GO

-- Tidy up

USE [master];

DROP DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2];

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ps. One Orange Chip Points: 29422 More actions · Answer 5

ps.

One Orange Chip

Points: 29422

September 6, 2009 at 11:35 pm

#1049983

Very Neat and Tidy script Paul 🙂

Pradeep Singh

Paul White SSC Guru Points: 150467 More actions · Answer 6

ps (9/6/2009)
Very Neat and Tidy script Paul 🙂

Thank you Pradeep!

I had a few minutes spare and your read-only file group idea made me think of a few things which I wanted to demo in a script.

So thanks for the inspiration!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kumarabhi281 Grasshopper Points: 15 More actions · Answer 7

hi this is abhi ,

simply write this sql query on your sql prompt:

connect scott/tiger

-- u must authorize as a sysdba:

lock table table_name in share mode;

u can also lock your table in exclusive mode.

raja.2012.raja SSC Enthusiast Points: 108 More actions · Answer 8

go to permissions on table properties then permissions then select user roles search button press then select public press ok then select grant what ever u want

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

Please note: 4 year old thread

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