SQL: What is REFERENCES permission and why does it matter?

I was at a client site the other day and they were puzzled by SQL Server object permissions. In particular, they couldn’t understand what the point of the REFERENCES permission is. It seemed pointless to them. It’s not. Let me explain why.

Keep in mind that if all the objects in the database have the same owner, then this isn’t an issue. But not all databases are like that. Many have different teams that control different parts of a large database.

Imagine that the Accounting team owns a Customers table. The Marketing team wants to create a LoyaltyPoints table and wants to add a foreign key to the Customers table. What permission do they need?

REFERENCES is the permission required.

But why do they need permission at all? It’s because after they create the foreign key, the Accounting team is now restricted in what they can do with their Customers table. For example, they can no longer truncate the table. If they want to drop and recreate the table, that won’t work. Before they can delete a row in the Customers table, it’s now a requirement that no related rows exist in the LoyaltyPoints table.

Because the owner of the table will be restricted by the foreign key, they need to grant at least REFERENCES permission to anyone who wants to create a foreign key to their tables.

2018-10-29