Reports on permissions for database objects, users, groups, or roles.
sp_helprotect [name [, username [, "grant" [,"none"|"granted"|"enabled"|role_name]]]]
is either the name of the table, view, stored procedure, SQLJ stored procedure, SQLJ function, or the 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.
displays permission information for the specified role only, regardless of whether this role has been granted to the user.
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
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, null, doctor, "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_role
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 sysusers, csmith, null, doctor_role, "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 sysattributes, rpillai, null, intern, "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.
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, or a role. 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.
Any user can execute sp_helprotect to view his or her own permissions. Only a System Security Officer can execute sp_helprotect to view permissions granted to other users.
System procedures sp_activeroles, sp_displayroles
