none
How to lock a table in SQL Server 2008

    Question

  • Hi all,

         I want to locka table inside my SQL Server 2008 Database instance from being Modified its schema or modify, Insert or delete any Records by these commands. Any Help will be appreciated. Thx in advance.

     

     

    Nilkanth

    Monday, May 17, 2010 3:15 AM

Answers

  • Nilkanth,

    If understand your question clearly, you want to know how to protect a protect a table from any schema modification and record modifications.

           Isolate and  place the  particular table on a Readonly Filegroup

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Monday, May 17, 2010 4:10 AM
  • An existing filegroup can be swithed from Read Write to Read Only and vice versa. If you have many tables in your filegroup and you want to only mark one table as read only, then you could create a new filegroup, add a data file to that filegroup, and move the table into that file group using INSERT INTO...SELECT command. Before you do this, you will have to create the table in the new file group and also script out the other objects in the table like indexes, constraints etc. Once the data is moved, you can mark the filegroup as readonly.

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Monday, May 17, 2010 10:25 AM
  • Nilkanth

    To mark an existing filegroup as READ_ONLY, please use the ALTER DATABASE command with MODIFY FILEGROUP option. Please see Books Online for details.

    To move a table to the new filegroup (which of course should be done before marking the FG as READ_ONLY), the best way is to rebuild the clustered index on the new filegroup, using the DROP_EXISTING and ON <filegroup> options. This will drop and recreate the clustered index in a single operation on the specific filegroup, and wherever the clustered index is, there is the data. 

    If you don't have a clustered index, and absolutely don't want one, you can try Amit's suggestion to use INSERT INTO ... SELECT, but it requires creating a new table on the desired filegroup, copying the data into that table, resetting any permissions, recreating dependent objects, rebuilding any nonclustered indexes, and renaming the new table to the original name.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Monday, May 17, 2010 8:40 PM
    Moderator

All replies

  • Nilkanth,

    If understand your question clearly, you want to know how to protect a protect a table from any schema modification and record modifications.

           Isolate and  place the  particular table on a Readonly Filegroup

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Monday, May 17, 2010 4:10 AM
  • Nilkanth

    Do you want to lock that the table for some period of time (user doing some DML and others are not allowed to have an access) or  all the time?

    Monday, May 17, 2010 6:55 AM
    Answerer
  • Nilkanth

    Do you want to lock that the table for some period of time (user doing some DML and others are not allowed to have an access) or  all the time?

    Monday, May 17, 2010 6:55 AM
    Answerer
  • Thanks for your Reply.

         I need to lock this table for a longer time may be for next 1 Year. By this time I want to keep this Table only in Read Only Mode. This is not for DML execution time.

    Thabks ,

    Nilkanth

    Monday, May 17, 2010 10:08 AM
  • Thanks Sivprasad,

             This sounds good. But one more que. wheather can I create a readonly file group or set the existing file group as read only. And second how can I transfer my existing Table to new file group with all data. Answers to these two questions will resolve this issue.

    Thanks for your answer,

     

    Nilkanth  

    Monday, May 17, 2010 10:14 AM
  • An existing filegroup can be swithed from Read Write to Read Only and vice versa. If you have many tables in your filegroup and you want to only mark one table as read only, then you could create a new filegroup, add a data file to that filegroup, and move the table into that file group using INSERT INTO...SELECT command. Before you do this, you will have to create the table in the new file group and also script out the other objects in the table like indexes, constraints etc. Once the data is moved, you can mark the filegroup as readonly.

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Monday, May 17, 2010 10:25 AM
  • Ok, then please follow Sivaprasad advise
    Monday, May 17, 2010 12:45 PM
    Answerer
  • There are many ways to accomplish not allowing updates to the table.

    In addition to the ones discussed, I would suggest moving the table to a new database and setting the database to "read only".  If the table is required in the local database, you could create a view to access to table:

    CREATE VIEW dbo.tablename

    SELECT * FROM newdatabase.dbo.tablename

     

     

    Monday, May 17, 2010 8:29 PM
    Moderator
  • Nilkanth

    To mark an existing filegroup as READ_ONLY, please use the ALTER DATABASE command with MODIFY FILEGROUP option. Please see Books Online for details.

    To move a table to the new filegroup (which of course should be done before marking the FG as READ_ONLY), the best way is to rebuild the clustered index on the new filegroup, using the DROP_EXISTING and ON <filegroup> options. This will drop and recreate the clustered index in a single operation on the specific filegroup, and wherever the clustered index is, there is the data. 

    If you don't have a clustered index, and absolutely don't want one, you can try Amit's suggestion to use INSERT INTO ... SELECT, but it requires creating a new table on the desired filegroup, copying the data into that table, resetting any permissions, recreating dependent objects, rebuilding any nonclustered indexes, and renaming the new table to the original name.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Monday, May 17, 2010 8:40 PM
    Moderator