sp_helprotect (Transact-SQL)

  • Article

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

Important

sp_helprotect does not return information about securables that are introduced in SQL Server 2005. Use sys.database_permissions and fn_builtin_permissions instead.

Topic link iconTransact-SQL Syntax Conventions

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, that has 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 one of the following statements:

    • 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 principal for which permissions are returned. security_account is sysname, with a default of NULL, which returns all principals in the current database. security_account must exist in the current database.
  • [ @grantorname = ] 'grantor'
    Is the name of the principal that granted permissions. grantor is sysname, with a default of NULL, which returns all information for permissions granted by any principal in the database.
  • [ @permissionarea = ] 'type'
    Is a character string that indicates whether to display object permissions (character string o), statement permissions (character string s), or both (os). type is varchar(10),with a default of os. type can 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 principal to which permissions were granted.

Grantor

sysname

Name of the principal that granted permissions to the specified grantee.

ProtectType

nvarchar(10)

Name of the type of protection:

GRANT REVOKE

Action

nvarchar(20)

Name of the permission:

  • REFERENCES
  • SELECT
  • INSERT
  • DELETE
  • UPDATE
  • CREATE TABLE
  • CREATE DATABASE
  • CREATE FUNCTION
  • 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 changed (by using the ALTER statement) on the object in the future.

All+New = Combination of All and New.

Remarks

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

If some but not all 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 database owner (dbo), execute the following:

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

Requires membership in the public role.

Information returned is subject to restrictions on access to metadata. Entities on which the principal has no permission do not appear. For more information, see Metadata Visibility Configuration.

Examples

A. Listing the permissions for a table

The following example lists the permissions for the titles table.

EXEC sp_helprotect 'titles'

B. Listing the permissions for a user

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

EXEC sp_helprotect NULL, 'Judy'

C. Listing the permissions granted by a specific user

The following example lists all permissions that were granted by user Judy in the current database, and uses NULL as a placeholder for the missing parameters.

EXEC sp_helprotect NULL, NULL, 'Judy'

D. Listing the statement permissions only

The following example lists all the statement permissions in the current database, and uses NULL as a placeholder for the missing parameters.

EXEC sp_helprotect NULL, NULL, NULL, 's'

See Also

Reference

Security Stored Procedures (Transact-SQL)
DENY (Transact-SQL)
GRANT (Transact-SQL)
REVOKE (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance