TSQL Error: EXECUTE permission denied on XML Column

I got this error when I created a restricted login for a user. We've only recently been optimizing the use of XML data types in SQL Server so it took me an hour to find the solution -- not on MSDN but on some other programmer's blog (gave my thanks of course! and the link appears on the bottom of this post).

Error we get:
System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object {XMLSchema1}, database {DatabaseName}, schema 'dbo'.

Solution was simple:
GRANT EXECUTE ON XML SCHEMA COLLECTION::{XMLSchema1} TO {RestrictedLoginName}

Thanks to Bob Beauchemin.

1 comment:

Arthur Zubarev said...

Thanks for a concise and clear post on how to fix this nuance. Very convoluted error, especially when the XML schema name is misleading!