sp_helprotect (Transact-SQL)sp_helprotect (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

返回一个报表,报表中包含当前数据库中某对象的用户权限或语句权限的信息。Returns a report that has information about user permissions for an object, or statement permissions, in the current database.

重要

sp_helprotect不返回有关中SQL Server 2005 (9.x)SQL Server 2005 (9.x)引入的安全对象的信息。sp_helprotect does not return information about securables that were introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x). 改为使用database_permissionsfn_builtin_permissionsUse sys.database_permissions and fn_builtin_permissions instead.

不列出始终分配给固定服务器角色或固定数据库角色的权限。Does not list permissions that are always assigned to the fixed server roles or fixed database roles. 不包括基于其在角色中的成员身份接收权限的登录名或用户。Does not include logins or users that receive permissions based on their membership in a role.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

[ @name = ] 'object_statement'当前数据库或语句中具有报告权限的对象的名称。[ @name = ] 'object_statement' Is the name of the object in the current database, or a statement, that has the permissions to report. object_statementnvarchar (776),默认值为 NULL,表示返回所有对象和语句权限。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.

如果object_statement是语句,则它可以是 CREATE 语句。If object_statement is a statement, it can be a CREATE statement.

[ @username = ] 'security_account'为其返回权限的主体的名称。[ @username = ] 'security_account' Is the name of the principal for which permissions are returned. security_account的数据值为sysname,默认值为 NULL,表示将返回当前数据库中的所有主体。security_account is sysname, with a default of NULL, which returns all principals in the current database. 当前数据库中必须存在security_accountsecurity_account must exist in the current database.

[ @grantorname = ] 'grantor'被授予权限的主体的名称。[ @grantorname = ] 'grantor' Is the name of the principal that granted permissions. 授权者为sysname,默认值为 NULL,它返回数据库中任何主体授予的权限的所有信息。grantor is sysname, with a default of NULL, which returns all information for permissions granted by any principal in the database.

[ @permissionarea = ] 'type'一个字符串,该字符串指示是显示对象权限(字符串o)、语句权限 (字符串) 还是同时显示两者(os)。[ @permissionarea = ] 'type' Is a character string that indicates whether to display object permissions (character string o), statement permissions (character string s), or both (os). 类型varchar (10),默认值为 " os"。type is varchar(10),with a default of os. 类型可以是os的任意组合,无论在os之间有或不包含逗号或空格。type can be any combination of o and s, with or without commas or spaces between o and s.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

列名称Column name 数据类型Data type 说明Description
所有者Owner sysnamesysname 对象所有者的名称。Name of the object owner.
ObjectObject sysnamesysname 对象的名称。Name of the object.
被授权者Grantee sysnamesysname 被授予权限的主体的名称。Name of the principal to which permissions were granted.
授权者Grantor sysnamesysname 向指定的被授权者进行授权的主体的名称。Name of the principal that granted permissions to the specified grantee.
ProtectTypeProtectType nvarchar (10)nvarchar(10) 保护类型的名称:Name of the type of protection:

GRANT REVOKEGRANT REVOKE
ActionAction nvarchar (60)nvarchar(60) 权限的名称。Name of the permission. 依赖于对象类型的有效的权限语句。Valid permission statements depend upon the type of object.
Column sysnamesysname 权限的类型:Type of permission:

All = 权限适用于对象所有的当前列。All = Permission covers all current columns of the object.

New = 权限适用于任何以后可以在对象上进行更改(使用 ALTER 语句)的新列。New = Permission covers any new columns that might be changed (by using the ALTER statement) on the object in the future.

All+New = All 和 New 的组合。All+New = Combination of All and New.

如果权限类型不适用于列,则返回一个期间。Returns a period if the type of permission does not apply to columns.

备注Remarks

以下过程中的所有参数都是可选的。All the parameters in the following procedure are optional. 如果不使用参数执行 sp_helprotect,则显示当前数据库中所有已经授予或拒绝的权限。If executed with no parameters, sp_helprotect displays all the permissions that have been granted or denied in the current database.

如果指定了一部分参数而不是指定全部参数,则使用命名参数来标识特定的参数,或者使用 NULL 作为占位符。If some but not all the parameters are specified, use named parameters to identify the particular parameter, or NULL as a placeholder. 例如,若要报告授权者数据库所有者 (dbo) 的所有权限,请执行: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';  

输出报表按权限类别、所有者、对象、被授权者、授权者、保护类型类别、保护类型、操作以及列连续 ID 进行排序。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. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

示例Examples

A.A. 列出某个表的权限Listing the permissions for a table

以下示例列出 titles 表的权限。The following example lists the permissions for the titles table.

EXEC sp_helprotect 'titles';  

B.B. 列出某个用户的权限Listing the permissions for a user

以下示例列出当前数据库中用户 Judy 所拥有的所有权限。The following example lists all permissions that user Judy has in the current database.

EXEC sp_helprotect NULL, 'Judy';  

C.C. 列出由某个特定用户授予的权限Listing the permissions granted by a specific user

以下示例列出当前数据库中由用户 Judy 授予的所有权限,并使用 NULL 作为缺少的参数的占位符。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.D. 仅列出语句权限Listing the statement permissions only

以下示例列出当前数据库中的所有语句权限,并使用 NULL 作为缺少的参数的占位符。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';   

e.在“新建 MySQL 数据库”边栏选项卡中,接受法律条款,然后单击“确定”。e. 列出 CREATE 语句的权限Listing the permissions for a CREATE statement

下面的示例列出授予了 CREATE TABLE 权限的所有用户。The following example list all users who have been granted the CREATE TABLE permission.

EXEC sp_helprotect @name = 'CREATE TABLE';  

另请参阅See Also

安全存储过程 (Transact-sql) Security Stored Procedures (Transact-SQL)
DENY (Transact-sql) DENY (Transact-SQL)
GRANT (Transact-SQL) GRANT (Transact-SQL)
REVOKE (Transact-sql) REVOKE (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)