Rajendra Gupta
Output of INFORMATION_SCHEMA.Views

View Definition Permissions in SQL Server

July 9, 2019 by

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.

Script View

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.

Generate Script Wizard

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

Choose an object in Generate Script Wizard

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

    View definition using t-SQL

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

    Output of an EXEC sp_helptext

  • 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

    Output of an object_definition function

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.

Pubclic role of a user in the database

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

Connect to SQL server using user with public access

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.

Error due to insufficient rights

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.

Output of INFORMATION_SCHEMA.Views

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

SSMS does not show objects due to permission issues

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

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

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

  • 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

  • To grant View Definition rights to a specific user and an object for a particular database

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.

Output of EXEC sp_helptext

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 see the object after providing 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

Output of sp_helprotect to verify permission

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

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

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

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

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

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views