Use sp_helprotect to report on permissions by database object or by user, and (optionally) by user for a specified object. Any user can execute this procedure. The syntax is:
sp_helprotect [name [, username [, "grant" [,"none"|"granted"|"enabled"|role_name]]]]]
where:
name – is either the name of the table, view, or stored procedure, or the name of a user, group, or role in the current database. If you do not provide a name, sp_helprotect reports on all permissions in the database.
username – is a user’s name in the current database.
If you specify username, only that user’s permissions on the specified object are reported. If name is not an object, sp_helprotect checks whether name is a user, group, or role and if it is, lists the permissions for the user, group, or role. If you specify keyword grant, and name is not an object, sp_helprotect displays all permissions granted by with grant option.
grant – displays the permissions granted to name with grant option.
none – ignores roles granted to the user.
granted – includes information on all roles granted to the user.
enabled – includes information on all roles activated by the user.
role_name – displays permission information for the specified role only, regardless of whether this role has been granted to the user.
For example, suppose you issue the following series of grant and revoke statements:
grant select on titles to judy grant update on titles to judy revoke update on titles(contract) from judy grant select on publishers to judy with grant option
To determine the permissions Judy now has on each column in the titles table, enter:
sp_helprotect titles, judy
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 price 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
The first row shows that the Database Owner (“dbo”) gave Judy permission to select all columns of the titles table. The rest of the lines indicate that she can update only the columns listed in the display. Judy cannot give select or update permissions to any other user.
To see Judy’s permissions on the publishers table, enter:
sp_helprotect publishers, judy
In this display, the grantable column indicates TRUE, meaning that Judy can grant the permission to other users.
grantor grantee type action object column grantable ------- ------ ----- ------ ------ ------ ------- dbo judy Grant Select publishers all TRUE
