sp_helprotect

Description

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

Syntax

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

Parameters

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.

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.

permission_name

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.

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

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

Example 7

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)

Example 8

Advises that SQLJ function access is public:

sp_helprotect function_sqlj
Implicit grant to public for SQLJ functions.

Example 9

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

Usage


sp_helprotect and encrypted columns

sp_helprotect reports new information on encrypted columns, encryption keys, and users as follows:

Permissions

Any user can execute sp_helprotect to view his or her own permissions. Only the SSO can view permissions for all users.

Auditing

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

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands grant, revoke

System procedures sp_activeroles, sp_displayroles