Returns a report that has information about user permissions for an object, or statement permissions, in the current database.
Name of the object owner.
Name of the object.
Name of the principal to which permissions were granted.
Name of the principal that granted permissions to the specified grantee.
Name of the type of protection:
Name of the permission:
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.
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
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.
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.
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'