sp_helprotect

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

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

Or

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'

See Also

DENY

GRANT

REVOKE

System Stored Procedures