EXECUTE permissions needed for XML Schema Collections

I’m working in depth with the new XML data type in SQL 2005 for the first time. There are a number of aspects that I really like. In particular the ability to strongly type the column using an XSD as the validation. For what I’m doing (it involves allowing a client to extend and persist database tables), they are almost perfect.

As you work through this process, however, it’s important to be aware of the permissions that are required to utilize the schema. Once I had added my XSD to the XML Schema Collection, I tried to INSERT a record into the table. I received the following exception:

System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'OrdersExtension', database 'Northwind', schema 'dbo'

It turns out that in order to use the schema, the user needs to be granted EXECUTE rights. Specifically, the following statement needed to be executed:

GRANT EXECUTE on XML SCHEMA COLLECTION::dbo.OrdersExtension to DBUser.

Whoo-hoo.  Problem solved.