How to Grant REFERENCES permission to all tables

Question

I have to grant REFERENCES permission to a login say sql_login.

I can give grant REFERENCES permission to individual table like

GRANT REFERENCES ON Mytable TO sql_login

Is there any way to grant REFERENCES permission to my login to all my tables or am wrong in any way

Answer 1

As it turns out, an explicit securable isn't needed in a GRANT statement. That is, you can say:

GRANT REFERENCES TO [sql_login];

Note, that it's not just tables to which the REFERENCES permission applies. From the documentation, it also applies to:

• AGGREGATE
• ASSEMBLY
• ASYMMETRIC KEY
• CERTIFICATE
• CONTRACT
• DATABASE
• FULLTEXT CATALOG
• FULLTEXT STOPLIST
• FUNCTION
• MESSAGE TYPE
• PROCEDURE
• QUEUE
• RULE
• SCHEMA
• SEARCH PROPERTY LIST
• SEQUENCE OBJECT SYMMETRIC KEY
• SYNONYM
• TABLE
• TYPE
• VIEW and
• XML SCHEMA COLLECTION

I say that only so that you appreciate that you may be granting permissions on objects that you don't intend to with this approach. But the upside is that you won't have to manage these permissions ever again. That is, if/when you add a new table to your database, your user will automatically get the REFERENCES permission for it.