\nI've always been slightly puzzled by the permission "EXECUTE" on an XML SCHEMA COLLECTION. Say I have an XML SCHEMA COLLECTION named MySchemas and a table that uses it:\n<\/p>\n
\nCREATE TABLE MyDocuments (
\n id INT PRIMARY KEY IDENTITY,
\n thexml XML (MySchemas)
\n)\n<\/p>\n
\nAnd suppose I have a user named FRED that I grant access to the TABLE:\n<\/p>\n
\nGRANT SELECT, INSERT, UPDATE, DELETE on MyDocuments to FRED\n<\/p>\n
\nNow FRED can DELETE and SELECT from the table, but because he does not have EXECUTE permission on the XML SCHEMA COLLECTION, he can't INSERT or UPDATE the column that uses it with a schema-valid document. He also can't use typed parameters or variables. UNLESS the value of the XML column, variable, or parameter is NULL. So this works for FRED…\n<\/p>\n
\nINSERT MyDocuments VALUES(NULL) — INSERTing a non-NULL would fail.\n<\/p>\n
\nSo does this:\n<\/p>\n
\nDECLARE @x XML (MySchemas)\n<\/p>\n
\nBut assignment of a document that corresponds to the XML SCHEMA COLLECTION to the variable @x fails unless FRED has EXECUTE on the XML SCHEMA COLLECTION. You grant the permission like this:\n<\/p>\n
\nGRANT EXECUTE ON XML SCHEMA COLLECTION::MySchemas TO FRED\n<\/p>\n
\nI can only guess that the reason for this privilege is to keep folks from "probing" the XML SCHEMA COLLECTION (perhaps there is sensitive information in the restrictions) by seeing what works on a column insert or variable assignment and what doesn't. And following the validation error messages. Perhaps.\n<\/p>\n
\nBTW, the Books Online (I have the December update) gets it right except for SELECT. It says:\n<\/p>\n
\nDenying the EXECUTE permission denies the principal the ability to insert or update the values in columns, variables, and parameters that are typed or constrained by the XML schema collection. It also denies the principal the ability to query the values in those same xml type columns and variables.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":" I've always been slightly puzzled by the permission "EXECUTE" on an XML SCHEMA COLLECTION. Say I have an XML SCHEMA COLLECTION named MySchemas and a table that uses it: CREATE TABLE MyDocuments ( id INT PRIMARY KEY IDENTITY, thexml XML (MySchemas) ) And suppose I have a user named FRED that I grant access […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,37],"tags":[],"class_list":["post-877","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-xml"],"yoast_head":"\n