sp_helprotect (version 6.5)

Reports on user permissions for an object and includes SELECT, execute, and access permissions for a given object.

For additional syntax information for sp_helprotect, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

sp_helprotect [OwnerObject_Statement_Name_Pattern [, GranteeName [, GrantorName [, PermissionsCategories]]]]

where

OwnerObject_Statement_Name_Pattern
Is the name of an object and/or statement for which permissions can be granted: the parameter PermissionCategories determines which permissions will be granted to the object or statement. The name can be any pattern understandable to the LIKE operator.

For more information on the LIKE operator, see Wildcard Characters in the Microsoft SQL Server Transact-SQL Reference.

An object name of 'tb217[_]Customer' can be used to focus on one table. For a broader report use 'tb%' or '%cust%'. An owner name can be used to qualify the object name, such as in 'dbo.vwBudget' or 'Patia%.pc%'. A database name cannot be used as a qualifier in this parameter; instead, rely upon the current database context. The default is '%'.

GranteeName
Is the name of the person who has permission to access the database object. This value can be any pattern that is understood by the LIKE operator (NULL is not accepted). As the GRANT statement syntax implies, users, guest, groups, and public can all be grantees. The default is '%'.
GrantorName
Is the name of the grantor to the database object. The grantor is anyone who has authority to grant another person (the grantee) access to an object. This value can be any pattern that is understood by the LIKE operator (NULL is not accepted). Only members of the users group can be grantors. The default is '%'.
PermissionsCategories
Is a string of single letters that determines which categories will be reported. This determines the interpretation of the first parameter. These are the supported categories of permissions.
Categories Permissions
O Object.
S Statement.
OS All categories are available (the default).

Remarks

The output report is sorted by permission category, owner, object, grantee, grantor, protection type category, protection type, action, and column sequential ID.

These are the special values that can appear in the report.

Value Description
(All) Indicates that the privilege covers all current columns of the object.
(New) Indicates that the privilege covers any new columns that might be altered (by using the ALTER statement) on the object in the future.
(All+New) Indicates a combination of All and New.

The width of each column in the output report will be reduced to the length of the longest data value under each column. No data values are truncated.

If this procedure completes successfully, 0 is returned. If an error occurs, 1 is returned.

Example

This example reports all permissions except those for DBO objects that begin with the letter T and grantor names that begin with the letter d.

sp_helprotect 'dbo.[^Tt]%',default,'[^d]%','O'

Permission

Execute permission is granted to the public group during installation or upgrade.