All we need is an easy explanation of the problem, so here it is.
The original problem is: a user, who has the permission to create/alter view granted is getting this message when trying to perform either operation:
Cannot find the type ‘nvarchar’, because it does not exist or you do
not have permission.
Through some internet digging I found it may be caused because he does not have GRANT REFERENCE permission on types. However,
select * from sys.types order by name
Returns over 2000 records, and GRANT REFERENCE command accepts a single type as parameter. How can I grant this user the reference permission for ALL types?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
If you need to do something like this, just use dynamic SQL to generate the command.
SELECT t.name,
'GRANT REFERENCES ON TYPE::'
+ SCHEMA_NAME(t.schema_id)
+ '.'
+ t.name
+ ' TO public;' AS command_to_run
FROM sys.types AS t;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0