locking table

  • Hi,

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

    regards,

    kannak.....

  • 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

  • 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

  • maybe you can use deny permission ?

  • 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];

  • Very Neat and Tidy script Paul 🙂



    Pradeep Singh

  • 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

  • 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.

  • 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

  • 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

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

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