sp_helprotect

Reports on permissions for database objects, users, groups, or roles.

Syntax

sp_helprotect [name[, username[, "grant" 
	[,"none" | "granted" | "enabled" | role_name[, permission_name]]]]]

Parameters

Examples

Usage

  • sp_helprotect reports permissions on a database object. If you supply the username parameter, only that user’s permissions on the database object are reported. If name is not an object, sp_helprotect checks to see if it is a user, a group, a role, or a permission name. If it is, sp_helprotect lists the permissions for the user, group, or role.

  • sp_helprotect looks for objects and users in the current database only.

  • If you do not specify an optional value such as granted, enabled, none, or role_name, the SAP ASE server returns information on all roles activated by the current specified user.

  • If the specified user is not the current user, the SAP ASE server returns information on all roles granted to the specified user.

  • Displayed information always includes permissions granted to the group in which the specified user is a member.

  • In granting permissions, a system administrator is treated as the object owner. If a system administrator grants permission on another user’s object, the owner’s name appears as the grantor in sp_helprotect output.

sp_helprotect reports information on encrypted columns, encryption keys, and users as follows:
  • Tables and columns – reports who has been granted decrypt permission and on which columns.

  • Encryption keys – reports who has been granted select permission.

  • Users – indicates users who have been granted create encryption key permission.

sp_helprotect reports information on predicated privileges by listing the name of the predicated privilege, if any, as an extra column in the output.

See also grant, revoke in Reference Manual: Commands.

Permissions

Any user can execute sp_helprotect. Permission checks do not differ based on the granular permissions settings.

Auditing

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

InformationValues
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

Related reference
sp_activeroles
sp_displayroles