SQL Source Control 7

Set up object locking

Object locking is only compatible with SQL Server 2008 and later.

Object locking isn't compatible with SQL Server's In-Memory OLTP features (memory optimized tables).
This is because SQL Server does not allow the use of triggers with databases that use memory optimized tables (see Microsoft's documentation)

Object locking isn't compatible with Amazon RDS or Azure SQL Database.

To set up object locking, you need to run a script on the server. Afterwards, you can lock objects in all databases on the server.

About the setup script

The script adds the trigger RG_SQLSourceControl_DDLTrigger to the server. The trigger detects when somebody tries to edit a locked object.

The script also creates the database Redgate with the schema SQLSourceControl. This contains:

  • the table SQLSourceControl.LockedObjects, to store locks on objects
  • the function SQLSourceControl.GetLockedObjects, to retrieve locked objects
  • the function SQLSourceControl.IsObjectLocked, to check if objects are locked
  • the stored procedure SQLSourceControl.LockObject, to lock objects
  • the stored procedure SQLSourceControl.UnlockObject, to unlock objects

To set up object locking from SQL Source Control

  1. In the Locking tab in SQL Source Control, click Open setup script.
  2. Click Run script.
    The script opens in a new Management Studio query window.
  3. Close any other query windows that use the RedGate database.
  4. Run the script.

Object locking is set up for the server.

Required privileges

To run the setup script, you need these database privileges:

  • CREATE USER
  • CREATE ROLE
  • GRANT ANY PRIVILEGE
  • CREATE ANY TABLE
  • CREATE ANY PROCEDURE

You don't need these privileges to lock and unlock objects, but you do need permission in the RedGate database to EXECUTE the following procedures/functions:

  • SQLSourceControl.LockObject
  • SQLSourceControl.UnlockObject
  • SQLSourceControl.LockedObjects
  • SQLSourceControl.IsObjectLocked

Didn't find what you were looking for?