SQL Source Control 7

Lock an object

You can lock objects you're working on so other people can't edit them. This means teams don't accidentally overwrite work.

After you lock an object, you can work on it as normal. When you try to edit an object locked by someone else, the server returns an error.

You can always unlock objects locked by other people.

If you're the only person who works on the databases on the server, you don't need to use object locking.

If developers regularly modify the same database objects, consider adopting a dedicated database development model for your team. 

Setting up

To use object locking, you need to run a SQL script on the server. You can open the script from the Locking tab in SQL Source Control.

For more information, and to review the script, see Set up object locking.

Compatible object types

Only these object types can be locked:

  • Defaults
  • Queues
  • Rules
  • Scalar-valued functions
  • Stored procedures
  • Synonyms
  • Tables
  • Table-valued functions
  • User-defined data types
  • User-defined table types
  • User-defined types (from CLR)
  • Views
  • XML schema collections

Locking objects

In the Object Explorer, right-click the object you want to lock, and select Lock this object:

The object is listed in the Locking tab:

Other people can't edit the object until it's unlocked. They can unlock the object if they need to. 

Alternative: using SQL

Run the following SQL, replacing each value with the relevant details:

EXEC [Redgate].[SQLSourceControl].[LockObject] 
   @db_name = 'the database name'
  ,@schema_name ='dbo'
  ,@object_name ='the object name'
  ,@object_type = 'the type of object'
  ,@login_name = 'your login details'

Unlocking objects

Anyone can unlock any locked object, no matter who locked it.

From the Locking tab

  1. In SQL Source Control, go to the Locking tab.
    This tab lists the objects that are locked on the database selected in SQL Source Control:

  2. Select the objects you want to unlock and click Unlock.

From the Object Explorer

Right-click the object you want to unlock and select Unlock this object.

Using SQL

Run the following SQL, replacing each field with the relevant details:

EXEC [Redgate].[SQLSourceControl].[UnlockObject] 
   @db_name ='the database name'
  ,@schema_name ='schema name (eg dbo)'
  ,@object_name ='name of the object'

Didn't find what you were looking for?