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, user-defined role, or group in the current database. If you do not provide a name, sp_helprotect reports on all permissions in the database.
is a user’s name in the current database.
displays the privileges granted to name with grant option.
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.
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 grantable ------- ------ ----- ------ ------ ------ --------- dbo judy Grant Select titles All FALSE dbo judy Grant Update titles advance FALSE dbo judy Grant Update titles notes FALSE dbo judy Grant Update titles pub_id FALSE dbo judy Grant Update titles pubdate FALSE dbo judy Grant Update titles title FALSE dbo judy Grant Update titles title_id FALSE dbo judy Grant Update titles total_sales FALSE dbo judy Grant Update titles type FALSE dbo judy Grant Select publishers all 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 grantable ------- ------- ------ ------- ------ ------ --------- dbo mary Grant Select titles advance TRUE dbo mary Grant Select titles price TRUE dbo mary Grant Update titles advance TRUE dbo mary Grant Update titles price 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 grantable ----------------------------------------------------------------- dbo doctor Grant Delete sysusers All FALSE dbo doctor Grant Insert sysusers All FALSE dbo doctor Grant References sysusers All FALSE dbo doctor Grant Select sysattributes All FALSE (1 row affected) (return status = 0)
Displays information about the permissions that the doctor role has in the database:
sp_helprotect doctor
grantor grantee type action object column grantable ----------------------------------------------------------------- dbo doctor Grant Delete sysusers All FALSE dbo doctor Grant Insert sysusers All FALSE dbo doctor Grant References sysusers All FALSE dbo doctor Grant Select sysattributes All FALSE (1 row affected) (return status = 0)
Displays information on all roles granted to “csmith”:
sp_helprotect csmith, null, null, "granted"
grantor grantee type action object column grantable ----------------------------------------------------------------- dbo csmith Grant Update sysusers All FALSE dbo doctor Grant Delete sysusers All FALSE dbo doctor Grant Insert sysusers All FALSE dbo doctor Grant References sysusers All 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 grantable --------------- ----------------------------------------------- dbo public Grant Select sysattributes All 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 grantable ------- -------- ----- ------- ------- ------ --------- sa1 hr_login Grant Decrypt employee ssn TRUE sa1 hr_role Grant Decrypt employee ssn 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 new 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.
Any user can execute sp_helprotect to view his or her own permissions. Only the SSO can view permissions for all users.
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
