Post reply

How to temporarily block a table from user access ?

  • February 10, 2015 at 11:26 am

    #398005

    I need to run a script in production that adds primary key to a table. Because table is large, I can't run it in one shot, the log file is not so large to accommodate it. Instead, I created a new table with same structure plus new surrogate primary key, and I populate it in a cursor loop.

    I already ran it many times in test server, and no problems with that. But the problem will be in production when applications will be accessing this table and try to insert/update it while I am running my loop.

    So I am looking for a solution how to block users to access this table, it's OK if they receive an error. Setting database to single user mode will not work because I don't want to block them from all the rest tables in this database.

    I am looking for a solution from SQL Server side, I don't have control over application server.

    Thanks.

  • February 10, 2015 at 12:12 pm

    #1776730

    Well if you don't care about giving users an error... rename the table do what you need to do rename it back.... or depending on how your accounts are set up just deny everyone access to that table.

  • February 10, 2015 at 12:52 pm

    #1776736

    start a transaction with tablockX? that would lock everyone out, right?

    begin tran

    select * from MyPrimaryTable with (tablockX) WHERE 1 = 2

    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!

  • February 10, 2015 at 1:50 pm

    #1776755

    Thank Lowell, I updated my script with this hint.

Viewing 4 posts - 1 through 4 (of 4 total)

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