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?

2
  • 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
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;
2
  • 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 ¯_(ツ)_/¯ Mar 22 '19 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.