0

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?

Yuropoor
  • 105
  • 4
  • 2
    I think this is a XY problem. Can you post the alter he is trying to execute on the view? There might be a typo that's making SQL Server think that NVARCHAR is a type instead of a common data type. – EzLo Mar 22 '19 at 11:22
  • I cannot post the query, however I can execute it just fine - but I do have elevated permissions on the server. – Yuropoor Mar 22 '19 at 11:46

1 Answers1

2

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;
Erik Darling
  • 40,781
  • 14
  • 130
  • 456
  • 1
    This script is handy for UDTs but I'd like to see a repro where it's required for the built in nvarchar system type called out in the question. – Dan Guzman Mar 22 '19 at 12:26
  • @DanGuzman I would too, but apparently we're not getting one here ¯_(ツ)_/¯ – Erik Darling Mar 22 '19 at 12:29