System Stored Procedures

Adaptive Server 15.7 contains new and changed system procedures.

New system stored procedures
System stored procedures Description
sp_merge_dup_inline_default

Removes existing duplicate inline default objects, converting the unique inline defaults to sharable inline default objects

sp_opt_querystats

Returns a performance analysis for the selected query

sp_securityprofile

Lists the attributes or bindings associated with a login profile

sp_showoptstats

Extracts and displays statistics and histograms for various data objects from system tables such as systabstats and sysstatistics

Changed system stored procedures
System stored procedures Description
sp_dboption
  • enforce dump tran sequence – prevents operations that disallow a subsequent dump transaction
  • allow wide rows –configures databases to allow wide, variable-length data-only locked (DOL) rows
  • full logging for all – fully log commands that are minimally logged by default (select into, alter table, and reorg rebuild)
sp_displaylogin
Displays
  • The login profile name associated with a login account.
  • The name of the default login profile if there is no login profile directly associated with the login account but there is a default login profile

The login overrides the sp_addlogin and sp_modifylogin default database, default language, authenticate with and login script parameters.

If login profiles are ignored, or there is no login profile associated to the login account either directly or through a default login profile, sp_displaylogin displays information in the format of versions earlier than 15.7.

sp_displayroles
  • When run against the current login, sp_displayroles displays the roles granted to the login profile to which it is associated. sp_displayroles requires the sso_role to view the roles associated with other login profiles.
  • Displays the roles granted to logins through an associated login profile. A Grantee column in the output indicates the login profile name, as applicable. sp_displayroles displays the Grantee column only if the login has an associated login profile with roles granted to it.
  • Displays the date when the role was locked, the reason for the lock, and the login ID that locked the role. For password protected roles, sp_displayroles displays the role password encryption version.
sp_encryption
  • When run by the SSO, key custodian, or the DBO, reports that a key is protected by dual control
  • sp_encryption helpkey, master and sp_encryption helpkey, 'dual master' report information about the master and dual master keys, including the existence of a copy encrypted for automatic startup and the existence of a recovery copy
  • mkey_startup_file [, {<new_path> | default_location | null} [, {sync_with_mem | sync_with_qrm}] – displays or sets the master key startup file name and path
  • downgrade_kek_size [, {“true” | “false”}] – displays or sets downgrade_kek_size configuration for the server
sp_help Displays compression settings at column, table, and partition level. Displays the in-row LOB settings at the column and table level.
sp_helpconstraint

Updated to display information about shareable inline defaults

sp_helprotect
  • Accepts 'master key' and 'dual master key' as object names
  • Accepts 'Set Encryption Passwd' as a valid permission name
  • Displays dual and master key permissions
sp_helpuser display_object lists all objects and user-defined datatypes owned by name_in_db in the current database
sp_locklogin Exempted login accounts are no longer locked because of inactivity.
sp_passwordpolicy
  • keypair regeneration period – specifies the date and time to start the first keypair generation and subsequent frequency of keypair regeneration
  • keypair error retry wait/count – specifies the various configurations you can set for regenerating a key pair after a failed attempt
sp_serveroption
Changes the definition for these options:
  • use message confidentiality – Sets message confidentiality for all connections to the remote server using Kerberos authentication
  • use message integrity –Sets message integrity for all connections to the remote server using Kerberos authentication

See the Reference Manual: Procedures.