sql server定义_在SQL Server中查看定义权限

sql server定义

We have various database objects such as view, stored procedures, triggers, functions and indexes in a relational database. Many times, we want to view definitions for these objects. We can use either SSMS graphical way or t-SQL to generate scripts.

我们在关系数据库中有各种数据库对象，例如视图，存储过程，触发器，函数和索引。 很多时候，我们想查看这些对象的定义。 我们可以使用SSMS图形方式或t-SQL生成脚本。

For example, we want to view the definition of a SQL view [HumanResources].[vEmployee]. Let’s explore both ways to generate scripts.

例如，我们要查看SQL视图[HumanResources]。[vEmployee]的定义。 让我们探索两种生成脚本的方式。

查看对象定义的不同方法 (Different methods to view the definition of objects)

SSMS脚本向导： (SSMS Script Wizard:)

Expand the database and go to Views. Right-click on a particular view for which we want to generate script and click on Script View as ->Create To.

展开数据库，然后转到“视图”。 右键单击我们要为其生成脚本的特定视图，然后单击“脚本视图为->创建到”。

We can get the script in the following ways.

我们可以通过以下方式获取脚本。

1. In the new query window
   在新的查询窗口中
2. Get the script in the .SQL file
   在.SQL文件中获取脚本
3. Copy the script in the clipboard
   将脚本复制到剪贴板中
4. Get script in a SQL Agent job
   在SQL Agent作业中获取脚本

生成脚本向导： (Generate Script Wizard:)

We can use Generate Script Wizard in SSMS as well to generate script. Right-click on a database and go to Tasks -> Generate Scripts.

我们也可以在SSMS中使用“生成脚本向导”来生成脚本。 右键单击数据库，然后转到任务->生成脚本。

In the generate script wizard, select the specific database object and click on Next.

在“生成脚本”向导中，选择特定的数据库对象，然后单击“下一步”。

You can complete the wizard to get the script.

您可以完成向导以获取脚本。

使用t-SQL： (Using t-SQL:)

We can use t-SQL queries as well to get the script of the objects. You can use the following t-SQL methods to get definitions for an object.

我们也可以使用t-SQL查询来获取对象的脚本。 您可以使用以下t-SQL方法获取对象的定义。

• Get scripts using the Information_Schama.Views: 使用Information_Schama.Views获取脚本：

  Execute the following query in the source database and specify the object name in the where clause

  在源数据库中执行以下查询，并在where子句中指定对象名称

  SELECT TABLE_NAME as ViewName,
  VIEW_DEFINITION as ViewDefinition
  FROM INFORMATION_SCHEMA.Views
  where TABLE_NAME='vEmployee'
  

  

• Sp_helptext system procedure: Sp_helptext系统过程：

  You can use sp_helptext system procedure as well to get the script. You need to specify the object name along with the schema if it is other than dbo.

  您也可以使用sp_helptext系统过程来获取脚本。 如果不是dbo，则需要指定对象名称以及模式。

  EXEC sp_helptext '[HumanResources].[vEmployee]'
  

  

• object_definition function object_definition函数

  We can use an object_definition function as well to generate a script for the object. In the following query, we use the object_definition function for a view vEmployee in the AdventureWorks2017 database

  我们还可以使用object_definition函数为该对象生成脚本。 在以下查询中，我们对AdventureWorks2017数据库中的视图vEmployee使用object_definition函数

  SELECT object_definition (OBJECT_ID(N'[HumanResources].[vEmployee]'))
  

  

生成对象脚本所需的权限 (Permissions required to generate objects script)

It is an essential aspect for any DBA to control the user permissions for accessing the objects. Many times users require additional rights on a database to perform their duty. By default, users with public role do not have permissions to view the definition of an object. It is useful for the developers to get the object definitions so that they can execute this in a non-production environment. We also do not want to give privileged permissions to users, especially in the production environment.

对于任何DBA来说，控制用户访问对象的权限都是至关重要的方面。 很多时候，用户要求数据库具有其他权限才能执行其职责。 默认情况下，具有公共角色的用户无权查看对象的定义。 对于开发人员来说，获取对象定义非常有用，这样他们就可以在非生产环境中执行此定义。 我们也不想授予用户特权，尤其是在生产环境中。

Let’s create a new database user and provide a public role in the AdventureWorks2014 database.

让我们创建一个新的数据库用户，并在AdventureWorks2014数据库中提供公共角色。

Connect to SQL Server using the login credentials having Public role permission.

使用具有公共角色权限的登录凭据连接到SQL Server。

Execute the query to get the view definition of an object. The command sp_helptext gives an error message that an object does not exist in the database.

执行查询以获取对象的视图定义。 命令sp_helptext给出错误消息，指出数据库中不存在对象。

If we try to get the script using INFORMATION_SCHEMA.Views, it does not give any error message; however, it does not return any row.

如果我们尝试使用INFORMATION_SCHEMA.Views获取脚本，它不会给出任何错误消息； 但是，它不返回任何行。

You cannot use the SSMS as well because it does not show the objects for the public role access.

您也不能使用SSMS，因为它不会显示用于公共角色访问的对象。

We can use View Definition permission in SQL Server to allow users to view the object definitions. We can either provide this access to a public role or an individual user.

我们可以在SQL Server中使用“查看定义”权限，以允许用户查看对象定义。 我们可以为公共角色或个人用户提供此访问权限。

• USE master 
  GO 
  GRANT VIEW ANY DEFINITION TO PUBLIC
  

• USE master 
  GO 
  GRANT VIEW ANY DEFINITION TO Rajendra
   
  

• USE AdventureWorks2017
  GO 
  GRANT VIEW ANY DEFINITION TO PUBLIC
  

• USE AdventureWorks2017
  GO 
  GRANT VIEW ANY DEFINITION TO Rajendra
  

• GRANT VIEW DEFINITION on [HumanResources].[vEmployee] TO Rajendra
  

Let’s provide access to a specific user (Rajendra) on a specific object ( [HumanResources].[vEmployee]) and verify the permissions to view the definition of an object.

让我们提供对特定对象（ [HumanResources]。[vEmployee]） 上特定用户（ Rajendra）的访问权限，并验证查看对象定义的权限。

EXEC sp_helptext '[HumanResources].[vEmployee]'

You can try other methods to view object definitions specified in the previous section. You can refresh connection in SSMS as well to view all objects after assigning the View Definition permissions.

您可以尝试其他方法来查看上一节中指定的对象定义。 分配“查看定义”权限后，您也可以在SSMS中刷新连接以查看所有对象。

We can keep track of the permissions using the sp_helprotect command.

我们可以使用sp_helprotect命令跟踪权限。

USE AdventureWorks2017
GO 
sp_helprotect

• Object: vEmployee
  对象：vEmployee
• Owner( Schema) : HumanResources
  所有者（架构）：人力资源
• Grantee ( User): Rajendra
  受赠者（用户）：Rajendra
• Grantor ( permission Grantor): dbo
  授予者（许可授予者）：dbo
• Permission: Grant
  许可：授予
• Action( rights): View Definition
  动作（权限）：查看定义

撤消“视图定义”权限 (Revoke View Definitions permission )

We learned to Grant the View definition permissions to a user, role or object in SQL Server in the previous section. It is also an important aspect to know how to revoke these View Definitions permissions. Many times, we might want to give temporary access to a user and revoke it later. We can revoke the permissions to the user across all databases with the Revoke View Any Definition command.

在上一节中，我们学习了向SQL Server中的用户，角色或对象授予View定义权限。 了解如何撤消这些“视图定义”权限也是一个重要方面。 很多时候，我们可能想给用户一个临时访问权，以后再撤消它。 我们可以使用“撤消查看任何定义”命令撤消所有数据库中对用户的权限。

• USE master 
  GO 
  REVOKE VIEW ANY DEFINITION TO PUBLIC
  

• USE master 
  GO 
  REVOKE VIEW ANY DEFINITION TO Rajendra  --Specify the user name
  

• USE AdventureWorks2017 --Specify the database name
  GO 
  REVOKE VIEW ANY DEFINITION TO PUBLIC
  

• USE AdventureWorks2017
  GO 
  REVOKE VIEW ANY DEFINITION TO Rajendra
  

• USE AdventureWorks2017
  GO 
  REVOKE VIEW DEFINITION on [HumanResources].[vEmployee] TO Rajendra
  

结论 (Conclusion)

In this article, we explored Grant and Revoke view definition permissions in SQL Server to view definitions for an object. It provides you with the necessary information to manage the permissions for object definitions. If you have any comments or questions, feel free to leave them in the comments below.

在本文中，我们探讨了SQL Server中的Grant和Revoke视图定义权限，以查看对象的定义。 它为您提供必要的信息，以管理对象定义的权限。 如果您有任何意见或疑问，请随时将其留在下面的评论中。

翻译自: https://www.sqlshack.com/an-overview-of-a-view-definition-permission-in-sql-server/

sql server定义