sp_objectpermission System Procedure

Generates a report on object permissions granted to the specified role, or user name, or the object permissions granted on the specified object or dbspace.

Syntax

dbo.sp_objectpermission (
[object_name],
[object_owner],
[object_type] )

Arguments

Arguments Description
object_name The name of an object or dbspace or a user or a role. If not specified, object permissions of the current user are reported. Default value is NULL.
object_owner The name of the object owner for the specified object name. The object permissions of the specified object owned by the specified object owner are displayed. This parameter must be specified to obtain the object permissions of an object owned by another user or role. Default value is NULL.
object_type

Valid values are:

  • TABLE*
  • VIEW
  • MATERIALIZED VIEW
  • SEQUENCE
  • PROCEDURE
  • FUNCTION
  • DBSPACE
  • USER
Note: *Column-level object permissions also appear.

If no value is specified, permissions on all object types are returned. Default value is NULL.

Result Set

Column Name Data Ttype Description
grantor char(128) The user ID of the grantor
grantee char(128) The user ID of the grantee
object_name char(128) The name of the object
object_type char(20) The type of object
column_name char(128) The name of the column
permission char(20) The name of the permission
grantable char(1) Whether or not the permission is grantable

Remarks

All arguments are optional and can generate these reports:
  • If input is an object (table, view, procedure, function, sequence, and so on), procedure displays list of all roles and user that have different object permission on the object.
  • If input is a role or user, procedure displays list of all object privileges granted to the role or input. When executing sp_objectpermission to display object permissions of a user or a role, the object permissions that are inherited through role grants also.
  • If input is a dbspace name, procedure displays list of all user or roles that have CREATE permission on the specified dbspace.
  • By default, object type is NULL and the object permissions for all existing object types matching the specified object name appear.

Privileges

  • Any user can execute sp_objectpermission to obtain all the object permissions granted to him- or herself,
  • Object owners can execute sp_objectpermission to obtain the object permissions for self-owned objects.
  • MANAGE ANY OBJECT PRIVILEGE system privilege is required to obtain object permissions that are granted:
    • On objects owned by other users
    • To other users
    .
  • MANAGE ANY OBJECT PRIVILEGE system privilege or role administrator is required to obtain object permissions that are granted:
    • On objects owned by a role
    • To a role.
  • MANAGE ANY DBSPACE system privilege required to obtain permissions of a dbspace.

Example

The following GRANT statements are executed:

GRANT SERVER OPERATOR TO r4;
GRANT BACKUP DATABASE TO r3 WITH ADMIN OPTION;
GRANT DROP CONNECTION TO r3 WITH ADMIN ONLY OPTION;
GRANT MONITOR TO r2;GRANT CHECKPOINT TO r1;
GRANT ROLE r2 TO r1 WITH ADMIN OPTION;
GRANT ROLE r3 TO r2 WITH NO ADMIN OPTION;
GRANT ROLE r4 TO r3 WITH ADMIN ONLY OPTION;
Consider these object permissions:
  • r5 owns a table named test_tab and a procedure named test_proc in the database.
  • u5, which has administrative rights over r5, grants the following permissions:
    • GRANT SELECT ON r5.test_tab TO r2 WITH GRANT OPTION;
    • GRANT SELECT (c1), UPDATE (c1) ON r5.test_tab TO r6 WITH GRANT OPTION;
    • GRANT EXECUTE ON r5.test_proc TO r3;
  • u6, which has administrative rights over r6, grants the following permissions:
    • GRANT SELECT (c1), REFERENCES (c1) ON r5.test_tab TO r3;
If sp_objectpermission( ‘r1’ ) is executed, output is similar to:
grantor grantee object_name owner object_type column_name permission grantable
u5 r2 test_tab r5 TABLE NULL SELECT Y
u6 r3 test_tab r5 COLUMN c1 SELECT N
u6 r3 test_tab r5 COLUMN c1 REFERENCES N
u6 r3 test_proc r5 PROCEDURE NULL EXECUTE N

If sp_objectpermission( ‘test_tab’, ‘r5’, ‘table’ ) is executed, output is similar to:

granter grantee object_name owner object_type column_name permission grantable
u5 r2 test_tab r5 TABLE NULL SELECT Y
u5 r6 test_tab r5 COLUMN c1 SELECT Y
u5 r6 test_tab r5 COLUMN c1 UPDATE Y
u6 r3 test_tab r5 COLUMN c1 SELECT N
u6 r3 test_tab r5 COLUMN c1 REFERENCES N
Parent topic: Alphabetical List of Catalog Stored Procedures