sp_helprotect

Description

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

Syntax

sp_helprotect [name [, username [, "grant" 
	[,"none"|"granted"|"enabled"|role_name]]]]

Parameters

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.

username

is a user’s name in the current database.

grant

displays the privileges granted to name with grant option.

none

ignores roles granted to the user when determining permissions granted.

granted

includes information on all roles granted to the user when determining permissions granted.

enabled

includes information on all roles activated by the user when determining permissions granted.

role_name

displays permission information for the specified role only, regardless of whether this role has been granted to the user.

Examples

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

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.

Usage

Permissions

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