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
    Commented Mar 22, 2019 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
    Commented Mar 22, 2019 at 11:46

1 Answer 1

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
    Commented Mar 22, 2019 at 12:26
  • @DanGuzman I would too, but apparently we're not getting one here ¯_(ツ)_/¯ Commented Mar 22, 2019 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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