View Definition Permissions in SQL Server
View Definition Permissions in SQL Server
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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolView Definition Permissions in SQL Server
July 9, 2019 by Rajendra Gupta 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 jobGenerate 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 1234 SELECT 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. 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 123 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 1234 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 123 USE AdventureWorks2017GO 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 123 USE AdventureWorks2017GO 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. 1 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. 123 USE AdventureWorks2017GO sp_helprotect 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 DefinitionRevoke 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 123 USE master GO REVOKE VIEW ANY DEFINITION TO PUBLIC Script to revoke permissions for a specific user with a public role on all databases 123 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 123 USE AdventureWorks2017 --Specify the database nameGO REVOKE VIEW ANY DEFINITION TO PUBLIC Script to revoke permissions for a specific user with a public role on a specific databases 123 USE AdventureWorks2017GO REVOKE VIEW ANY DEFINITION TO Rajendra Script to revoke permissions for a specific user and an object for a particular database 123 USE AdventureWorks2017GO REVOKE VIEW DEFINITION on [HumanResources].[vEmployee] TO RajendraConclusion
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 GuptaHi! 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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022