Ever created an XML Schema collection before? Our developers work with a lot of XML so I wasn’t surprised when eventually a request came in about permissions with XML SCHEMA COLLECTION.
Surprised that they had a permissions issue, yes, but not surprised that they were working with XML.
Why is it “an XML” and not “a XML”?
For information purposes, I’d normally provide a brief description of what an XML SCHEMA COLLECTION is but, being completely honest, I’m still not sure I can vocalize it in an understandable way. It’s kind of like explaining the colour purple without using other colours (and yes, that’s colour with a ‘u’).
I know what it is, I just can’t explain it properly…yet
So what I’m going to do is point you to the link for Microsoft docs for XML Schema Collection (done) and just gloss right over it (nothing to see here).
Permissions Shane, you mentioned permissions.
Right, sorry.
Investigation first. This was on the Development server and they had emailed me the creation code along with the error message they had received, which was this guy:
CREATE XML SCHEMA COLLECTION name AS N'RANDOM XML ALERT...'
Msg 2797, Level 16, State 2, Line 20
The default schema does not exist.
However, when I ran the code, I got a different error message, mainly this guy:
CREATE XML SCHEMA COLLECTION name AS N'RANDOM XML ALERT...'
Msg 9459, Level 16, State 1, Line 3
XML parsing: line 2, character 34, undeclared prefix
Which meant I had to go back and tell them to fix their darn XML.
Now I’m pretty sure we have a problems though:
That error message did not fill me with confidence. Yeah, sure they had bad XML but I was now fairly sure that there was also a permissions problem. Mainly because if there’s one thing that I’ve learned so far, it’s this:
No good can come from two different people getting two different errors from the same code!
Proper XML:
Proper XML was provided and ran by the developers but the same error message came back…
CREATE XML SCHEMA COLLECTION name AS N'random xml alert...'
Msg 2797, Level 16, State 2, Line 20
The default schema does not exist.
The difference this time was, when I ran the code, I received the following message
Command(s) completed successfully.
…That’s not good.
Developers happy. DBAs not.
At this stage, I’m nearly convinced that it’s a permissions issue.
Checking the permissions required to create an XML Schema Collection doesn’t help, since the Devs were part of the db_dlladmin database role, so that should have been covered.
In my head I’m thinking of all the things that I can do to try and troubleshoot this problem.
- Extended Events my session,
- Ask my Senior DBA,
- Cry
Then I realize that I’m jumping the gun again and I slow down, and check the first error message again. This time without the developers shouting in my ear, about permissions.
The DEFAULT schema
That says “schema”, not “permission”. Maybe the difference between the DBAs and the Devs was to do with default schema and not permissions this time. Let’s check it out!
SELECT
IIF(principal_id = 1, 'DBA', 'Dev') AS DBPrincipal,
default_schema_name
FROM sys.database_principals
WHERE principal_id IN (1, 14);
Wait, so it was a SCHEMA issue?
Have you checked the Examples section of Microsoft Docs? Normally, they are a great source of material for examples but if you check out the examples for XML Schema Collection , not one of them shows the schema name in the examples.
So, I walk over to the original developer and his machine, change his code to…
CREATE XML SCHEMA COLLECTION dbo.name_test AS N'RANDOM XML ALERT...'
And it works!
Apparently what had happened was the Senior Dev had gotten sick of developers not specifying the schema when creating objects and had asked the Senior DBA to remove the default schema for Developers. That seems to work (by that I mean, everything error-ed out correctly), they were happy that developers now had to specify the schema, and life moved on.
Yet, later on, when the developer read the docs for XML Schema Collection, and saw that there was no schema in the examples, it didn’t cross their mind that a schema was required. So they didn’t specify it and that, in combination with no default schema, caused this whole mess.
The (fast food) takeaways:
- Slow down! Don’t jump the gun,
- Developers don’t know everything,
- It’s not always permissions,
- Schemas are important(!),
- Having checklists for investigations are highly useful, and
- Documentation, especially on past decisions, are even more useful!
Apologies for the blurb of a blog post but I have to go.
Apparently, there’s a permissions issue with a Stored Procedure now…