Reports on permissions for database objects, users, groups, or roles.


sp_helprotect [name [, username [, "grant" 



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.


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

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

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
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 

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, 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)

Example 5

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)

Example 6

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)

Example 7

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)

Example 8

Advises that SQLJ function access is public:

sp_helprotect function_sqlj

Implicit grant to public for SQLJ functions.



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.

See also

Commands grant, revoke

System procedures sp_activeroles, sp_displayroles