Reports on permissions for database objects, users, groups, or roles.
sp_helprotect [name[, username[, "grant" [,"none" | "granted" | "enabled" | role_name[, permission_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.
is the name of the user, group, or role in the current database.
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.
ignores roles granted to the user when determining permissions granted.
includes information on all roles granted to the user when determining permissions granted.
includes information on all roles activated by the user when determining permissions granted.
lists privileges granted through role_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.
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
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
Displays all the permissions that “judy” has in the database:
sp_helprotect judy
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
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
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)
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)
Advises that SQLJ function access is public:
sp_helprotect function_sqlj
Implicit grant to public for SQLJ functions.
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
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_720002565 FALSE dbo joffrey Grant Select tab1 All tab1_720002565 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_720002565 FALSE dbo robert Grant Select tab1 All pred1 FALSE dbo robert Grant Select tab1 All tab1_720002565 FALSE dbo robert Grant Update tab1 All pred1 FALSE
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, Adaptive Server returns information on all roles activated by the current specified user.
If the specified user is not the current user, Adaptive 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.
Any user can execute sp_helprotect. Permission checks do not differ based on the granular permissions settings.
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 |
|
System procedures sp_activeroles, sp_displayroles
