September 4, 2019 at 2:43 pm
I'm working on a new application that runs against a SQL Server 2016 SP2 database. We're using Entity Framework. One of my colleagues wrote a view that uses INFORMATION_SCHEMA. It refuses to open the view, giving me an access denied error. I'm wondering if entity framework has a problem trying to open that system view or not? For what it's worth, here's the view, which I generated from SSMS using the script to create command:
Create view [Core].[vwDataDictionary] as
SELECT ROW_NUMBER() OVER(order by A.[TABLE_CATALOG]) AS [ID]
,A.[TABLE_CATALOG]
,A.[TABLE_SCHEMA]
,A.[TABLE_NAME]
,A.[COLUMN_NAME]
,[ORDINAL_POSITION]
,[COLUMN_DEFAULT]
,[IS_NULLABLE]
,[DATA_TYPE]
,[CHARACTER_MAXIMUM_LENGTH]
,[CHARACTER_OCTET_LENGTH]
,[NUMERIC_PRECISION]
,[NUMERIC_PRECISION_RADIX]
,[NUMERIC_SCALE]
,[DATETIME_PRECISION]
,[CHARACTER_SET_CATALOG]
,[CHARACTER_SET_SCHEMA]
,[CHARACTER_SET_NAME]
,[COLLATION_CATALOG]
,[COLLATION_SCHEMA]
,[COLLATION_NAME]
,[DOMAIN_CATALOG]
,[DOMAIN_SCHEMA]
,[DOMAIN_NAME]
,B.[CONSTRAINT_NAME]
,C.[CONSTRAINT_TYPE]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS A
left join
[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] AS B
on A.TABLE_NAME = B.TABLE_NAME and A.COLUMN_NAME = B.COLUMN_NAME
left join
[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS C
on B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
where A.[TABLE_SCHEMA] = 'PW'
GO
Kindest Regards, Rod Connect with me on LinkedIn.
September 4, 2019 at 3:00 pm
The user needs "view definition" permissions on the database
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 4, 2019 at 6:23 pm
It's best to stop using INFORMATION_SCHEMA views. They can contain inaccuracies (on ownership) and are incomplete now.
Stick to sys.columns and the other MS standard views.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 5, 2019 at 6:00 pm
It's best to stop using INFORMATION_SCHEMA views. They can contain inaccuracies (on ownership) and are incomplete now.
Stick to sys.columns and the other MS standard views.
I'm not familiar with sys.columns. I'll have to look into it, for the reasons you mentioned. Is INFORMATION_SCHEMA going to be deprecated?
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply