GRANT REFERENCE on all user defined types

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

Leave a Reply