SQLLawman

I received a e-mail a few days ago from one of our developers informing me that he is unable to create views on one of our SQL Server 2005 databases.  He also told me he had another developer try and she also could not create views.  Interesting, I thought, so out to my favorite co-worker - Google.  My buddy Google quickly pointed me to this MSDN post.  This explained to me that when a user gains rights by Active Directory group membership they are not given a default schema.  Say what? Yes, that's right, because multiple AD groups could have rights on a database, and a user could be a member of multiple AD groups it could get confusing.  So any statement that requires a schema will fail with some form of the following error message.

Property DefaultSchema is not available for Database '[<database>]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (SQLEditors)

So the first solution to the problem I found was to create a login for the user on the database server, give him public rights to the database in question, then revoke connection rights to that user.  What a bunch of work, I'm much to lazy to do that kind of work on every database for every developer in our organization.  So I called upon another friend, I believe he has run into every possible SQL issue and lived to tell about it.  That, of course, is Denny Cherry.  He replied with a simple solution.  Have the developer prepend (I know, there are discussions about if this is a word or not.  But I like it so I'll use it.  I have never really been bothered by that.  Originally there were no words and people started using them and they became words.  Maybe....) the schema. to the view name during the creation.  I love it, it let's the developers resolve the problem.