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

name – is either the name of the table, view, stored procedure, SQLJ stored procedure, SQLJ function, user-defined function, name of a user, role, or group in the current database. If you do not provide a name, sp_helprotect reports on all permissions in the database.
username – is the name of the user, group, or role in the current database.
grant – displays the privileges granted on name to username with grant option. If username is null, sp_helprotect lists all privileges granted with grant option on name.
none – ignores roles granted to the user when determining permissions granted.
granted – includes information on all roles granted to the user when determining permissions granted.
enabled – includes information on all roles activated by the user when determining permissions granted.
role_name – lists privileges granted through role_name.
permission_name – allows sp_helprotect to provide information (grantor name, grantee name, table/column name, grantability) for any specific permission granted in a given database.
The value of this parameter can be any value from the sysprotects.action column.

Examples

Example 1 – This series of grant and revoke statements, executing sp_helprotect titles results in this display:

grant select on titles to judy 
grant update on titles to judy 
revoke update on titles(price) from judy
grant select on publishers to judy
with grant option
go
sp_helprotect titles

grantor grantee type  action   object   column        predicate  grantable
------- ------  -----   ------   ------   ------      ---------   ---------
dbo    judy     Grant   Select   titles   All         0           FALSE 
dbo    judy     Grant   Update   titles   advance     0           FALSE 
dbo    judy     Grant   Update   titles   notes       0           FALSE 
dbo    judy     Grant   Update   titles   pub_id      0           FALSE 
dbo    judy     Grant   Update   titles   pubdate     0           FALSE 
dbo    judy     Grant   Update   titles   title       0           FALSE 
dbo    judy     Grant   Update   titles   title_id    0           FALSE 
dbo    judy     Grant   Update   titles   total_sales 0           FALSE 
dbo    judy     Grant   Update   titles   type        0           FALSE
dbo    judy     Grant   Select   titles   all         0           TRUE

Example 2 – Issuing the following grant statement results in sp_helprotect displaying the following:

grant select, update on titles(price, advance)
	to mary
	with grant option
go
sp_helprotect titles

grantor   grantee   type    action   object   column   predicate grantable
-------   -------   ------  -------  ------   ------   ---------  --------
dbo       mary      Grant   Select   titles   advance  0          TRUE 
dbo       mary      Grant   Select   titles   price    0          TRUE 
dbo       mary      Grant   Update   titles   advance  0          TRUE 
dbo       mary      Grant   Update   titles   price    0          TRUE

Example 3 – Displays all the permissions that “judy” has in the database:
```
sp_helprotect judy
```

Example 4 – Displays any permissions that “csmith” has on the sysusers table, as well as whether “csmith” has with grant option which allows “csmith” to grant permissions to other users:

sp_helprotect sysusers, csmith, "grant"

grantor  grantee  type    action     object    column  predicate grantable
-------- -------  ------  ---------  --------  ------  --------- --------
dbo      doctor    Grant  Delete     sysusers  All     0         FALSE
dbo      doctor    Grant  Insert     sysusers  All     0         FALSE
dbo      doctor    Grant  References sysusers  All     0         FALSE

Example 5 – Displays information about the permissions that the doctor role has in the database:

sp_helprotect doctor

grantor  grantee  type   action     object   column   predicate  grantable
-------  ------  -----  ---------  --------  ------   -------   ----------
dbo      doctor  Grant  Delete     sysusers  All      0         FALSE 
dbo      doctor  Grant  Insert     sysusers  All      0         FALSE 
dbo      doctor  Grant  References sysusers  All      0         FALSE

Example 6 – Displays information on all roles granted to “csmith”:

sp_helprotect csmith, null, null, "granted"

grantor grantee   type   action     object    column  predicate  grantable
------- --------  ------ ---------- --------  ------- ---------- ---------
dbo     csmith    Grant  Update     sysusers  All     0          FALSE
dbo     doctor    Grant  Delete     sysusers  All     0          FALSE
dbo     doctor    Grant  Insert     sysusers  All     0          FALSE
dbo     doctor    Grant  References sysusers  All     0          FALSE

(1 row affected)
(return status = 0)

Example 7 – Displays information on all active roles granted to “rpillai”:

sp_helprotect rpillai, null, null, "enabled"

grantor grantee   type   action object         column  predicate grantable
------- --------  ------ ------ -------------  ------- --------- -------
dbo     public    Grant  Select sysattributes  All     0          FALSE

(1 row affected)
(return status = 0)

Example 8 – Advises that SQLJ function access is public:

sp_helprotect function_sqlj

Implicit grant to public for SQLJ functions.

Example 9 – Uses the action “Decrypt” from sysprotects.action:

sp_helprotect @permission_name = "Decrypt"

grantor  grantee  type   action   object     column   predicate  grantable
-------  -------- -----  -------  -------    ------   ---------  ---------
sa1      hr_login Grant  Decrypt  employee   ssn      0          TRUE
sa1      hr_role  Grant  Decrypt  employee   ssn      0          FALSE

Example 10 –

Displays the name of the predicated privilege in the output:

grant select, update, on tab1 where col1 = 8 as pred1 to robert
grant select, delete on tab1 where col1 = 9 to robert, joffrey
grant select, delete, update on tab1 where col2 = 10 as pred2 to role1,
group1

sp_helprotect tab1

grantor  grantee type  action object column predicate          grantable
-----------------------------------------------------------------------
 dbo     joffrey Grant Delete tab1   All    tab1_fdoIidqcSKLm  FALSE
 dbo     joffrey Grant Select tab1   All    tab1_fdoIidqcSKLm  FALSE
 dbo     group1  Grant Delete tab1   All    pred2              FALSE
 dbo     group1  Grant Select tab1   All    pred2              FALSE
 dbo     group1  Grant Update tab1   All    pred2              FALSE
 dbo     role1   Grant Delete tab1   All    pred2              FALSE
 dbo     role1   Grant Select tab1   All    pred2              FALSE
 dbo     role1   Grant Update tab1   All    pred2              FALSE
 dbo     robert  Grant Delete tab1   All    tab1_fdoIidqcSKLm  FALSE
 dbo     robert  Grant Select tab1   All    pred1              FALSE
 dbo     robert  Grant Select tab1   All    tab1_fdoIidqcSKLm  FALSE
 dbo     robert  Grant Update tab1   All    pred1              FALSE

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:

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