sp_helprotect (T-SQL)

Returns a report with information about user permissions for an object, or statement permissions, in the current database.

Syntax

sp_helprotect [[@name =] 'object_statement']
    [,[@username =] 'security_account']
    [,[@grantorname =] 'grantor']
    [,[@permissionarea =] 'type']

Arguments

[@name =] 'object_statement'

Is the name of the object in the current database, or a statement, with the permissions to report. object_statement is nvarchar(776), with a default of NULL, which returns all object and statement permissions. If the value is an object (table, view, stored procedure, or extended stored procedure), it must be a valid object in the current database. The object name can include an owner qualifier in the form owner.object.

If object_statement is a statement, it can be:

CREATE DATABASE
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG

[@username =] 'security_account'

Is the name of the security account for which permissions are returned. security_account is sysname, with a default of NULL, which returns all security accounts in the current database. security_account must be a valid security account in the current database. When specifying a Microsoft® Windows NT® user, specify the name the Windows NT user is known by in the database (added using sp_grantdbaccess).

[@grantorname =] 'grantor'

Is the name of the security account that has granted permissions. grantor is sysname, with a default of NULL, which returns all information for permissions granted by any security account in the database. When specifying a Windows NT user, specify the name that the Windows NT user is known by in the database (added using sp_grantdbaccess).

[@permissionarea =] 'type'

Is a character string indicating whether to display object permissions (character string o), statement permissions (character string s), or both (o s). type is varchar(10), with a default of o s. type may be any combination of o and s, with or without commas or spaces between o and s.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name	Data type	Description
Owner	sysname	Name of the object owner.
Object	sysname	Name of the object.
Grantee	sysname	Name of the person granted permissions.
Grantor	sysname	Name of the person who granted permissions to the specified grantee.
ProtectType	char(10)	Name of the type of protection: GRANT REVOKE
Action	varchar(20)	Name of the permission: REFERENCES SELECT INSERT DELETE UPDATE CREATE TABLE CREATE DATABASE CREATE RULE CREATE VIEW CREATE PROCEDURE EXECUTE BACKUP DATABASE CREATE DEFAULT BACKUP LOG
Column	sysname	Type of permission: All = Permission covers all current columns of the object. New = Permission covers any new columns that might be altered (by using the ALTER statement) on the object in the future. All+New = Combination of All and New.

Remarks

All of the parameters of this procedure are optional. If executed with no parameters, sp_helprotect displays all of the permissions that have been granted or denied in the current database.

If some, but not all of the parameters are specified, use named parameters to identify the particular parameter, or NULL as a placeholder. For example, to report all permissions for the grantor dbo, execute:

EXEC sp_helprotect NULL, NULL, dbo

EXEC sp_helprotect @grantorname = 'dbo'

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

Permissions

Execute permissions default to the public role.

Examples

A. List the permissions for a table

This example lists the permissions for the titles table.

EXEC sp_helprotect 'titles'

B. List the permissions for a user

This example lists all permissions that user Judy has in the current database.

EXEC sp_helprotect NULL, 'Judy'

C. List the permissions granted by a specific user

This example lists all permissions that were granted by user Judy in the current database, using a NULL as a placeholder for the missing parameters.

EXEC sp_helprotect NULL, NULL, 'Judy'

D. List the statement permissions only

This example lists all the statement permissions in the current database, using NULL as a placeholder for the missing parameters.

EXEC sp_helprotect NULL, NULL, NULL, 's'