Locking objects
Published 16 J 2015
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.
Setting up
To use object locking, you need to run a SQL script on the server. You can open the script from the Object locking tab in SQL Source Control.
For more information, and to review the script, see Setting 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 Object 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 [tempdb].[dbo].[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 Object locking tab
In SQL Source Control, go to the Object locking tab.
This tab lists all the objects that are locked on the database selected in SQL Source Control:- 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 [tempdb].[dbo].[UnlockObject] @db_name ='the database name' ,@schema_name ='schema name (eg dbo)' ,@object_name ='name of the object'