sp_droprowlockpromote

Syntax

sp_droprowlockpromote {"database" | "table"}, objname

database | table – specifies whether to remove the row lock promotion thresholds from a database or table.
objname – is the name of the database or table from which to remove the row lock promotion thresholds.

Example 1 – Removes the row lock promotion values from the sales table. Lock promotion for sales now uses the database or server-wide values:
```
sp_droprowlockpromote "table", "sales"
```

There are additional considerations when using sp_droprowlockpromote:

Use sp_droprowlockpromote to drop row lock promotion values set with sp_setrowlockpromote.
When you drop a database’s row lock promotion thresholds, datarows-locked tables that do not have row lock promotion thresholds configured use the server-wide values. Use sp_configure to check the value of the row lock promotion configuration parameters.
When a table’s row lock promotion values are dropped, the SAP ASE server uses the database’s row lock promotion thresholds, if they are configured, or the server-wide values, if no thresholds are set for the database.
To change the lock promotion thresholds for a database, you must be using the master database. To change the lock promotion thresholds for a table in a database, you must be using the database where the table resides.
You can change server-wide values with sp_setrowlockpromote. Since this changes the values in the row lock promotion configuration parameters, there is no corresponding server option for sp_droprowlockpromote.

The permission checks for sp_droprowlockpromote differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage lock promotion threshold` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect