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

locking table Expand / Collapse
Author
Message
Posted Saturday, September 5, 2009 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 26, 2012 2:41 AM
Points: 4, Visits: 36

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


regards,
kannak.....
Post #783311
Posted Saturday, September 5, 2009 5:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 2,242, Visits: 3,645
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
Post #783315
Posted Saturday, September 5, 2009 5:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 2,242, Visits: 3,645
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
Post #783316
Posted Sunday, September 6, 2009 12:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 3, 2010 3:50 AM
Points: 146, Visits: 178
maybe you can use deny permission ?
Post #783515
Posted Sunday, September 6, 2009 4:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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;
INSERT dbo.ExistingTable DEFAULT VALUES;
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #783549
Posted Sunday, September 6, 2009 11:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 2,242, Visits: 3,645
Very Neat and Tidy script Paul




Pradeep Singh
Post #783615
Posted Monday, September 7, 2009 3:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #783678
Posted Saturday, April 17, 2010 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 17, 2010 9:54 AM
Points: 1, Visits: 5
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.
Post #905420
Posted Sunday, March 31, 2013 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 1, 2013 11:27 PM
Points: 2, Visits: 22
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

Post #1437308
Posted Sunday, March 31, 2013 3:33 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: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
Please note: 4 year old thread


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 #1437335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse