Author Topic: VIEW DEFINITION Permission in SQL Server  (Read 37670 times)

Ritesh Shah

  • Administrator
  • Jr. Member
  • *****
  • Posts: 57
  • Ritesh Shah
    • View Profile
    • IT Technology Blog
VIEW DEFINITION Permission in SQL Server
« on: September 19, 2012, 01:23:25 PM »
As per MSDN: The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

If your user has db_reader permission in database and s/he tries to generate the schema of table/object, he won't be able to do it. you have to give him/her permission to do that and that is where VIEW DEFINATION comes to help.
Code: [Select]
USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'testing', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
USE [AdventureWorks2012]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestLogin]
GO

Now login with TestLogin to your SQL Server and try to generate schema for table, you won't be able to do it. let us give VIEW DEFINATION permission.

1.) database level

2.) Schema Level

3.) Individual Entity

Let us see each of this permission with TSQL Script.
Code: [Select]
--allow to generate script for all object under HumanResources Schema
GRANT VIEW DEFINITION ON SCHEMA :: HumanResources TO TestLogin;

--allow to generate script for all object under AdventureWOrks2012 database
GRANT VIEW DEFINITION ON DATABASE :: AdventureWOrks2012 TO TestLogin;

--allow to generate script for Person.Addres table
GRANT VIEW DEFINITION ON OBJECT :: Person.Address TO TestLogin;
Ritesh Shah
Administrator
Extreme-Advice.com
SQLHub.com

kaluahir16

  • Newbie
  • *
  • Posts: 20
    • View Profile
Re: VIEW DEFINITION Permission in SQL Server
« Reply #1 on: September 19, 2012, 03:51:30 PM »
It is very usefull.
Regards,
Kalu Bhuva