sp_droprowlockpromote

Removes row lock promotion threshold values from a database or table.

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.

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"

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, Adaptive 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.
Server-wide values can be changed with sp_setrowlockpromote. This changes the values in the row lock promotion configuration parameters, so there is no corresponding server option for sp_droprowlockpromote.

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

Granular permissions enabled	With granular permissions enabled, you must be a user with manage lock promotion threshold privilege.
Granular permissions disabled	With granular permissions disabled, you must be a user with sa_role.

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

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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