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.
- In the new query window
- Get the script in the .SQL file
- Copy the script in the clipboard
- 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.
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 clause1234SELECT TABLE_NAME as ViewName,VIEW_DEFINITION as ViewDefinitionFROM INFORMATION_SCHEMA.Viewswhere 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.1EXEC 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 database1SELECT 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
123USE masterGOGRANT 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
1234USE masterGOGRANT 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
123USE AdventureWorks2017GOGRANT 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
123USE AdventureWorks2017GOGRANT VIEW ANY DEFINITION TO Rajendra
- To grant View Definition rights to a specific user and an object for a particular database
1GRANT 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.
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.
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
123USE masterGOREVOKE VIEW ANY DEFINITION TO PUBLIC
- Script to revoke permissions for a specific user with a public role on all databases
123USE masterGOREVOKE VIEW ANY DEFINITION TO Rajendra --Specify the user name
- Script to revoke permissions for all users with a public role on a specific databases
123USE AdventureWorks2017 --Specify the database nameGOREVOKE VIEW ANY DEFINITION TO PUBLIC
- Script to revoke permissions for a specific user with a public role on a specific databases
123USE AdventureWorks2017GOREVOKE VIEW ANY DEFINITION TO Rajendra
- Script to revoke permissions for a specific user and an object for a particular database
123USE AdventureWorks2017GOREVOKE VIEW DEFINITION on [HumanResources].[vEmployee] TO Rajendra
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.
- Export Amazon Aurora MySQL or Amazon RDS snapshots to AWS S3 buckets - January 19, 2021
- SQL Server PolyBase external tables with Azure Blob Storage - January 15, 2021
- Database Cloning in Amazon Aurora - January 11, 2021