GRANT REFERENCE on all user defined types

Question

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?

Answer 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;