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.

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

Different methods to view the definition of objects

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
3. Copy the script in the clipboard
4. Get script in a SQL Agent job

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.

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

You can complete the wizard to get the script.

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.

• Get scripts using the Information_Schama.Views:

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

  1 2 3 4

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

  

• Sp_helptext system procedure:

  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.

  1	EXEC sp_helptext '[HumanResources].[vEmployee]'

  

• object_definition function

  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

  1	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.

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

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

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.

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.

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

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.

• If we want to provide view object definition rights to all users with public role, execute the following query. This query gives rights for all online databases in the instance
  1 2 3

  USE master GO GRANT VIEW ANY DEFINITION TO PUBLIC

• If we want to provide view object definition rights to a specific user with the public role on all databases, execute the following query
  1 2 3 4

  USE master GO GRANT VIEW ANY DEFINITION TO Rajendra

• If we want to give object definition for all users with a public role in a specific database, execute the following query
  1 2 3

  USE AdventureWorks2017 GO GRANT VIEW ANY DEFINITION TO PUBLIC

• If we want to provide view object definition rights to a specific user with a public role on a specific database, execute the following query
  1 2 3

  USE AdventureWorks2017 GO GRANT VIEW ANY DEFINITION TO Rajendra

• To grant View Definition rights to a specific user and an object for a particular database
  1	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.

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.

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

In the screenshot below, you can observe the following:
• Object: vEmployee
• Owner( Schema) : HumanResources
• Grantee ( User): Rajendra
• Grantor ( permission Grantor): 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.

• Script to revoke View Definition permissions for all users with a public role
  1 2 3

  USE master GO REVOKE VIEW ANY DEFINITION TO PUBLIC

• Script to revoke permissions for a specific user with a public role on all databases
  1 2 3

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

• Script to revoke permissions for all users with a public role on a specific databases
  1 2 3

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

• Script to revoke permissions for a specific user with a public role on a specific databases
  1 2 3

  USE AdventureWorks2017 GO REVOKE VIEW ANY DEFINITION TO Rajendra

• Script to revoke permissions for a specific user and an object for a particular database
  1 2 3

  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.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023