Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
server| Data | database | user RIGHTS | statement sp_helprotect
Returns a report that contains information about the user rights or statement permissions of an object in the current database.
sp_helprotect [[@name =] ' object_statement '] [, [@username =] ' security_account '] [, [@grantorname =] ' gr Antor '] [, [@permissionarea =] ' type ']
[@name =] ' Object_statement '
is the name of the object or statement whose permissions you want to report in the current database. The object_statement data type is nvarchar (776) and the default value is NULL, and this default value returns all objects and statement permissions. If the value is an object (a table, view, stored procedure, or extended stored procedure), it must be a valid object in the current database. An object name can contain an owner qualifier, in the form of Owner.object.
If Object_statement is a statement, you can:
CREATE databasecreate defaultcreate functioncreate procedurecreate rulecreate tablecreate VIEWBACKUP LOG
[@username =] ' Security_account '
is the name of the security account whose permissions are returned. The security_account data type is sysname and the default value is NULL, which returns all security accounts in the current database. Security_account must be a valid security account in the current database. When specifying microsoft®windows NT® users, specify the names that the Windows NT user can recognize in the database (added with sp_grantdbaccess).
[@grantorname =] ' Grantor '
is the name of the authorized security account. The grantor data type is sysname and the default value is NULL, which returns all information about the permissions granted by any security account in the database. When you specify a Windows NT user, specify the name that the Windows NT user can recognize in the database (added with sp_grantdbaccess).
[@permissionarea =] ' Type '
is a string indicating whether to display object permissions (String o), statement permissions (string s), or both (O s). Type is varchar (10) and the default value is O S. Type can be any combination of O and S, between O and S can or without commas or spaces.
Return code value
0 (Success) or 1 (failed)
The result set column name data type describes the name of the Ownersysname object owner. The name of the Objectsysname object. Granteesysname the name of the user to whom the permission is granted. Grantorsysname the name of the user to whom the specified authorized user is authorized. Protecttypechar (10) The name of the protection type:
Actionvarchar (20) The name of the permission:
Referencesselectinsertdeleteupdatecreate tablecreate databasecreate functioncreate RULECREATE VIEWCREATE Procedureexecutebackup databasecreate defaultbackup LOG
Type of columnsysname permission:
All = permissions apply to all current column of the object. The new = permission applies to any newer columns that can be modified on the object later (using the ALTER statement). The combination of all+new = All and New.
All of the parameters for this procedure are optional. If you perform sp_helprotect without parameters, all permissions that have been granted or denied in the current database are displayed.
If you specify a subset of the parameters, instead of specifying all the parameters, use named parameters to identify specific parameters, or use NULL as a placeholder. For example, to report all permissions for the grant dbo, perform:
EXEC sp_helprotect null, NULL, dbo
EXEC sp_helprotect @grantorname = ' dbo '
The output reports are sorted by permission category, owner, object, grantee, grantor, protection type classification, protection type, action, and column serial ID.
Execute permissions are granted to the public role by default.
Example A. List permissions for a table
The following example lists the permissions for table titles.
EXEC sp_helprotect ' titles '
B. Listing permissions for a user
The following example lists all the permissions that user Judy has in the current database.
EXEC sp_helprotect NULL, ' Judy '
C. List the permissions granted by a particular user
The following example lists all the permissions granted by user Judy in the current database, using NULL as a placeholder for missing parameters.
EXEC sp_helprotect null, NULL, ' Judy '
D. List only Statement permissions
The following example lists all statement permissions in the current database, using NULL as a placeholder for missing parameters.
EXEC sp_helprotect null, NULL, NULL, ' s '
This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or
reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or
complaint, to firstname.lastname@example.org. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
and provide relevant evidence. A staff member will contact you within 5 working days.