SQL ServerSo it was inevitable that I eventually end up with a post about some IT related issues that cropped up at work.  Yesterday’s was pretty great.  We are in the process of upgrading our Symantec Endpoint Protection (SEP) infrastructure and ran into issues that made it appear (both to us and Symantec) that we had database corruption.  And the only way to fix that is, you guessed it, disaster recovery.  In spite of how it sounds, the process ended up being fairly straightforward (thanks to us having all out ducks in a row).

However, even after getting our system up and running again with a fresh instance, we were still seeing the same DB errors.  Great, so what now?  To me this just screamed to me that we had been going down the wrong path the entire time.  It was not DB corruption but a problematic  reconfiguration that had crept into our system at some point over the past few weeks (I had performed a similar task without problem just weeks before).

So I fired up our trusty SQL Profiling tool.  Looking at the results, the first thing I noticed—almost immediately—was that the SQL statement that SEP was making was not fully qualified (it was missing the table schema).

select CONTENT from LOCAL_METADATA

instead of

select CONTENT from SCHEMA.LOCAL_METADATA

However even though the schema was missing, it shouldn’t have been an issue because the default schema we had set for the connecting user should have resolved this.  But that obviously wasn’t happening.  So what did we do?  We did what any self respecting IT professional would do, we asked our MS SQL database administrator.  But this behavior struck him as odd as well.

After hitting this brick wall we decided it was time to pull out my trusty Google search skills.  After a some quick searching I found someone else with a similar issue over on stackoverflow.com{{1}}.  One of the responses there provided us with the final clue to solve our problem.  They referenced a ‘note’ specified in the Microsoft documentation for the ALTER USER{{2}} commands.  There it states:

Important
The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

This coupled with the fact that the schema for these database tables was not dbo, meant that SQL server must have been defaulting to the wrong schema when the above unqualified query was made.  But why now and not before?  Well, during the upgrade preparations, one of our engineers read somewhere (we have yet to find that recommendation again) that our SEP db user needed to have the sysadmin role in order for the upgrade to go smoothly.  That little ‘tip’ ended up costing us hours of troubleshooting (and unnecessary disaster recovery).  But on the upside, we now know a little more about the default schema for anyone with the sysadmin role.  Good to know.

[[1]]http://stackoverflow.com/questions/3806245/sql-server-schema-and-default-schema[[1]]

[[2]]http://msdn.microsoft.com/en-us/library/ms176060.aspx[[2]]

Leave a reply

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> 

required