I have learned many things at every PASS Summit I have attended. First off, all of the people who are speakers are just human beings like you and me (including everyone's favorite Kalen Delaney). I learned that it is really expensive to put on a conference like PASS, and I have especially learned to enjoy six dollar bagels and four dollar cans of coke, while they still exist. And I have learned that speaking in front of 200-2000 people isn't nearly as scary as it initially seemed. I have even learned many things about being a DBA\Database Architect that I might never have learned when I was just digging around books online.

However, the "best thing" I have learned is how to be a citizen of the SQL Server community. PASS, being a non-profit organization relies very heavily on people to donate their time. Up until this year (I am teaching a post-con with Paul Nielsen), I have never made one cent at the PASS Summit, and last year I spent over a thousand of my own dollars to attend. Why? Because I believe in the SQL Server community that PASS represents. During the time I have spent in the PASS community I have made lots of great friends, and I wouldn't trade any of them or even one moment I have spent at any Summit for anything.

I hope to meet you there this year adding to the community!

Tagged, I was by the Rambling SQLSarg, Jonathan Kehayias, to answer the following question:

So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?

The funny part is that I have almost always been isolated like this at any of my jobs, unless a big project was underway and I was the roadblock that other people had to get their part of the project done.  I have at times had teammates that I worked with closely, one of them was an exceptional dba that had only been a dba for a year or two, and the others were more project managery, and once I worked with a UI guy who I worked with to build an excellent UI that was generated from a call to a stored procedure to get the metadata for the object.

So often I actually do this, planning what I think would be the best way to implement our current systems or future ones and how best they would be could be built using my own personal ways of doing stuff. Ironically, more often than not, it isn’t feasible to do in my own little world, but it becomes good advice for others (hopefully who don’t have large amounts of legacy code piled up to deal with.  In my real world, reality becomes that we have meetings and meetings to discuss and committee things until we eventually fix the legacy stuff enough to go for a good long time.  Sadly reality has a lot to do with money, available resources (human and computer), etc whereas in the mind of an architect, the only barrier during the pure design part of a project is physics. 

For example, take the following two designs of a house that an house architect might think up (perhaps after taking some oxycodone for a back (or hip) problem:

Clearly the idea in B would be nixed because even an architect knows enough about physics to know that this would tip over, where as A would be much safer.  (These images should also explain why I did not finish my degree in Civil Engineering.)

So, assuming that I am on this deserted island alone, with what would I research? Well, let’s just use a Genie clause and ignore the fact that just like Genie wouldn’t allow Aladin to wish for more wishes, I won’t state the obvious that I would study up on boat making and praying for rescue. And even assuming that I knew that someone was coming back for me (hopefully not the crazy Dr. Boris Balinkoff coming to change my brain with a duck), I would spend a good amount of my off time looking for something to surf with/something to avoid sunburn, and probably something good for lunch…

1. A really good explanation for Fifth Normal Form that didn’t include mind numbing technical language that makes you brain hurt.

I get the basic idea behind the fifth normal form, but unlike even the fourth normal form, I can’t come up with an explanation that isn’t basically the same as how your mom and dad explained why not to mess with the little red haired girl with the bad reputation.  “Don’t violate this rule, or else bad things will occur” 

“Bad things?”, you ask.

“Like babies, and shotguns” but at this point, you aren’t really sure how pulling the hair of the this little girl will really hurt things. And every explanation of the entire problem you can find on the internet (with safe search turned on!) is like a college course in anatomy. You of course simply know you will never do that so you ignore it.

All explanations for 5th Normal Form are either of the “don’t do it”, “doesn’t matter”, “or 30 pages of terse text with lots of formulas that look nothing like the CREATE TABLE statement you have grown to love over the years. 

2. Work on/Design/Create a data access layer tool that allows you to set up the objects that you want and generates procedures and functional code

Look, I understand your pain programmers.  You like things to be easy.  You don’t like to write a bunch of “code” to solve problems.  You like it when some tool dictates to you how things are going to work internal to your programs and you want to just let it happen.  We relational types are not really like that.  Sure, most compiled code nowadays will either run in 10 or 100 ns depending on how you write it, and no one really cares about 90 ns unless they are building a website along the scale of ESPN or MySpace. But in the relational world, we are dealing with fetching data from physical storage which is inherently s l o w. It gets better and better every year, but most companies react to this increase in performance by increasing their desire to store data at a pace that is slightly faster than the improvement in technology.

However, there are still a majority of companies out there that could still be using SQL Server 1.0 on 16 bit OS/2 (okay, I am exaggerating, maybe SQL 6.0) that don’t care so much about performance and just want to get programming done fast.  Tools often cater to these entities and not to to the larger systems needs.  As relational types, a majority of us constantly bellow “stored procedures…stored procedures” on the corner like the town crier.  The problem is, tools generally want to ignore this layer, primarily for time and programmer experience.  Why write SQL code when you can let the tool do it?

But if a tool can generate SQL, why can’t an object layer be built that automatically creates procedures? And like any generated code, if you don’t monkey with it, the generator can create a new version as you need it.  If you do have to make changes, likely to use some query technique that is not easily automated, the code generator just throws up it’s hands and says “I am trusting you brother” (or sister.)

Maybe then the topic of stored procs could finally be retired and we could just leave it alone.  This tool, the best on the market, gives you both in a manner that is EASY to do, FAST to build, and POWERFUL enough to do extremely complex configurations…

3.  Getting better at data warehousing.

I have designed and implemented a data warehouse, including ETL (both with the assistance of my friend and fellow Data Architect, Frank Castora). It was quite the mind bending experience, as normalization becomes a “bad thing” and denormalizaiton is a “good thing”.  From 15 years of OLTP development and writing, this felt like a sin that was going to end up with me getting a pitchfork to the tush for eternity.  But as the project went on, it started to feel natural and the dimensionally modeled tables stored in the SQL database started to feel semi-natural and really started to work for me.  But there were quite a few things that we did “wrong” as well as many that we did “not really wrong but not exactly correct either” that had to do with different scenarios that didn’t fit the examples that we could find on the web. 

Add to that that Analysis Services and the language MDX both were quite foreign to me that the project didn’t exactly “fail”, but it certainly didn’t make the “succeed” column either.  The project is more or less ongoing yet in a pattern of stasis that Dave Lister (not the guy I work with but the guy from Red Dwarf who was frozen for a million years only to wake up with everybody gone except for electronic holograms and computers) would appreciate. If I knew more about all of the processes/tools, I could go far towards making things better and getting things straight.

So, whom would I like to see deserted on an island? Um, I mean, whom would I like to see how they would respond to this challenge… hmm  Paul Nielsen doesn’t have anything to do these days (ha!), and Allen White is a very interesting guy who I would like to see his feelings too.

A setting that I noticed a while back when looking at sys.configurations was disallow results from triggers.  Triggers are one of my favorite subjects, and you will find a lot of good uses of them in my book (triggers are also well named, as poor usage of them will allow you to shoot your foot off in a heartbeat as I discussed in my earlier post: http://sqlblog.com/blogs/louis_davidson/archive/2008/07/13/triggers-evil.aspx).

One of the main ways you can mess up your system is to have triggers that return results, often by accident, that the client doesn’t know about.  There are three kinds of return values that are interesting:

• Result sets
• Raiserror messages
• Rowcount messages

Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases.  It is never a good idea to return data from a trigger, but it is actually pretty useful for testing.  In the following code, I will test this setting and show how it will affect returning data to the client from the aforementioned methods. 

What will the setting do? It will raise an error if you try to do a result set.  It will automatically suppress rowcount messages (without the need for SET NOCOUNT ON), but will allow error messages (I won’t cover it here, but error messages can cause issues with transactions in triggers, particularly when coupled with TRY…CATCH blocks).

To demonstrate the setting, I will use tempdb.  Checking the system setting for your server, use:

SELECT value
FROM    sys.configurations
WHERE name = 'disallow results from triggers'

This will probably return, unless you have discovered the setting before I did, in which case you probably aren’t reading any longer, so there.

---------
0

To show you the effect of this setting , let's build the following scenario. The trigger will return 3 types of values

• the contents of the table named triggerResult
• the rowcount of rows affected from an insert statement like you would normally have in a database
• the rowcount of the rows from the insert statement into a temp table

To start, we create 2 tables, one as the “main” table, and another that will hold the results of a side effect causing query:

--primary test table
create table triggerResult
(
    triggerResultId int primary key
)   
--holds our side effect to prove the trigger executed
create table triggerResultSideEffect
(
    triggerResultId int
)

Then we will create the trigger that gives us several different types of output

create trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    --returns a result set
    select triggerResultId
    from   triggerResult

    --side effect like you might expect in a trigger
    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end
Now we will test out the trigger by inserting one row into the triggerResult table:

insert into triggerResult
values (1)

This returns (the final rows affected is from the original statement):

triggerResultId
---------------
1

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Now try a multi-row operation, to see the difference

insert into triggerResult
values (2),(3),(4)

This returns the following (which you can clearly now see the different number of rows that were affected by each statement, 4 from the one that returns all of the rows in the table, 3 from the side effect query that inserts the rows from the inserted table.

triggerResultId
---------------
1
2
3
4

(4 row(s) affected)

(3 row(s) affected)

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(3 row(s) affected)

Now, change the setting to disallow trigger results (you may need to do allow advanced options)

exec sp_configure 'show advanced options',1
RECONFIGURE
exec sp_configure 'disallow results from triggers',1
RECONFIGURE

Now, just to make sure of where our data is, we know that there are 4 rows in the triggerResult table. Let’s check the triggerResultSideEffect table contents:

SELECT *
FROM   triggerResultSideEffect
ORDER  BY triggerResultId

This returns, showing all of the values we have inserted:

triggerResultId
---------------
1
2
3
4

Now, trying to run the statement with the same trigger:

insert into triggerResult
values (5)

This will cause the following error message:

Msg 524, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 6
A trigger returned a resultset and the server option 'disallow results from triggers' is true.

Altering the trigger, just remove the statement that returns a result set:

alter trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    ----returns a result set
    --select triggerResultId
    --from   triggerResult
    --side effect like you might expect in a trigger 

    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end

Now, re-executing the statement with no results being returned:

insert into triggerResult
values (5)

This simply returns the error message that are returned, and the rows affected message from the insert statement:

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Just to make sure that the rows were created:

select *
from   triggerResult

select *
from   triggerResultSideEffect

This returns:

triggerResultId
---------------
1
2
3
4
5

(5 row(s) affected)

triggerResultId
---------------
1
4
3
2
5

(5 row(s) affected)

Which shows that the data was inserted..

Note that books online states: This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

This means that the ability to return result sets from a trigger will go away in the next version of SQL Server.  Returning results from a trigger is certainly not something that has ever been a good practice, so if this does in fact occur, it is a good setting to set in your development servers and begin to use.

Ok, so you are the lead ______________ for your company.  The big cheese, the super duper guy who knows everything…right?  So then some newbie comes up with an idea that might solve a problem you have been dealing with for a few days/weeks/months/whatever.  What do you do?

A. Ignore his idea and use your own, genetically superior idea

B. Ignore where the idea came from, pretend it is your own.

C. Accept the answer, give the newbie credit, put it in your toolbox so next time you will know it too

If you didn’t answer C, I am glad we aren’t coworkers.

Frankly any other answer blows my mind. Clearly, the people who hire a person on the top of their field will know more than most people. And it should be expected that a person 15 years of experience with some topic/product will know more than people with 1 or 2. Everyone has access to the same search engines, so they can find new ideas and act upon them. Ideally there is a review process where people that have the life experience do much of the reviewing, but still that usually goes both ways.  Reviewing ideas before executing them should be done even if it is the companies chief smarty pants, if for no other reason that when people of lesser experience try to validate an “experts” ideas (or hopefully disprove the expert and win some cool points,) the worst that can happen is that someone learns something new.  The best thing that can happen is a disaster is averted. And that can’t be a bad thing, right?

But going back to the “expert” role.  Too often being saddled with the moniker “expert” gets mistaken for “perfect.”  Unless I suddenly become perfect, and man, that ain’t happening any time soon, everyone make mistakes. In fact, I know that one of the things I am most proud of is my complete lack of competitive pride. (I am the best, baby!)  To me, the key is how the person who is saddled with the title expert handles it. All to often, the gut reaction is A. “Well, you can’t be right. My idea is clearly better as it was formed from my superior intellect.” This might not be that horrible, like if the decision was where to eat lunch, but if you were the engineer who designed the Titanic, well, not good. The answer B is just evil.

Who cares if there are 2 experts at your company? Or on the planet? Competition should drive you to work harder to be better, with the final goal being everyone being better…not to harm others…

I have gotten a few more reviews in, and interestingly I appreciate the negative ones almost as much as the positive ones. I prefer the negative ones that have decent star ratings better… but what are you going to do.

The most recent review was critical of the book for not having mentioned testing. I actually think that this was really good criticism and have already started my planning for how to rectify this.  The only thing I wish this reviewer had done was mention the rest of the book.  This person has three reviews on Amazon and some cool stuff on his wish list (if we ever meet, I will be happy to buy you an expresso/cup of coffee and discuss the rest of the book, which pretty much goes for anyone, if you want. I will also buy you lunch at my favorite restaurant: Prince’s Hot Chicken Shack. Only rule is that we have to talk about the book at least a little).

Please, if you have read the book, oh please (am I begging), I beg you (yes, I am begging) to please email me your feelings on the book or post reviews. I would love to know what you thought of it. I just want to make the book better and who knows, I usually give out a few copies of the next book (no guarantees) if the advice is really constructive. 

The real problem here is that writing is a VERY slow process. If my book was electronic, I could start writing and shoehorn in the new material and be done with it.  But a book is not like a website.  I wrote the book as a cohesive 650+ pages that are supposed to work together as a unit. Unlike a set of web pages, my hope is that you will skim 1/2 of the book and read at least half (which half depends on you, but I like both halves.) And in each edition, I try to give more and more information as I find it, learn it, and on a few shining occasions, make something up.

In the first edition, the process was simply that I wrote what I thought I wanted to say, and editor(s) hacked that to bits.  So about 10 people were involved in the creation.  Now, working on the fifth edition I have had hundreds of people give me feedback, and a fairly small percentage tell me stuff that was missing that ruined the experience for them. I take these comments VERY seriously, especially if I agree with them.  If you compared version one to the fourth version, a lot of the stuff I was really enthused about didn’t make too many people all that excited, so I cut it.  In this last version, I have more examples, more code, and more technique, because it was asked for.

Now if I just knew if this reviewer liked anything else about, or if there was more that was disliked I could possibly make the next book even better.  So if you have any ideas/feelings/criticism/etc email them to louis@drsql.org.  Thanks!

This blog probably won’t stir up a hornet’s nest or anything, but I would also expect that it would be the least popular in practice. The person who feels they can disagree with the need for a reasonable amount of documentation is probably nuts. In the first post, I defined documented as “Anything that cannot be gathered from the previous four is written down and/or diagrammed for others”.  And yes, I know documentation is more boring than a whole oilfield of derricks.  So let me give you a life lesson. Documentation is fun when it is done BEFORE you build.

Yes, you heard me. If you have chosen names for things that seem right, the definitions you make may in fact just fall out of the name. So it isn’t that much work. The payoff is probably a lot better than you might imagine. And be careful though, TOO much documentation can easily be worse than no documentation. Sometimes systems have so much documentation that it is impossible to know how to even find information, or there are conflicting bits of documentation.  Keep it sparse, just enough information that you communicate what was on your mind when you were designing your objects. Some example benefits that I find:

• Names can only be so descriptive – You only get 128 characters for a name, and if you get close to that your users are going to beat you with whatever they can find handy.  Name should be succinct versions of the definitions. 
• Names should not usually include purpose - Purpose can be tricky. Who wanted this, why they wanted it, and how it is to be used can only be documented (or mined from the code later, I suppose, if you are REALLY patient and your programmers use really good comments.)
• While documenting your objects, you will be forced to (shudder) think one more time about what you are documenting -Writing down your meaning of what you have just designed after you are feeling good about it gives you a chance to give it one more look over, and this time in context of other stuff you have done. I often find one or two things per hundred (at least) that I go…maybe I was wrong. (Of course when I say it the sound in my head is more like Fonzie admitting he was wr..wr..wr..wroo…wwwr..Hey a guy has to have some ego.)
• Documentation should be written in a language that all users can understand – Sometimes in our names we use clever jargon to make the names more concise. Inheritance is a good example.  Ask the normal user what inheritance is any they will start fantasizing that their parents just passed away (in the best possible manner, of course) and left them a billion dollars. Clearly this is unlikely to be the meaning of this word unless you are creating a system to create a last will and testament.

The fourth is the magical moment (wow, too much Disney ).  You have your design you want to implement.  Your analysts and users are checking out the design to make sure they “approve.”  They see a lot of names, fine.  They see some diagrams, decent. They read your definitions and wait.  What do you mean by “this attribute is here for X”? Did we agree to that?

Well, yeah, I thought you did, you will indignantly think, but sometimes, no matter how sure you are that you are right (and really no matter how right you are that this was in fact the design you had agree on) the realization that a communication gap the size of you and your parents was somehow opened up and things went sideways.

I feel like I am repeating myself, but for you Agile guys, I don’t mean documenting things to death, or even spending all that much time.  If you are creating 30 tables with 10 columns on average, and 20 relationships, you only need to document 320 independent items. Add a bit more to the documentation for constraints, and then for processes, and you might have two hours of work. And if you DO find this taking too long, either you have messed up the first Pillar: Coherent or your team has real communication problems that can’t easily be solved by reading this blog.  And the more Agile you are, the more important coherent communication (which includes documentation) is. What happens if you get it wrong and cannot ship after a sprint because you didn’t get clear direction and your team didn’t realize it until they started seeing the “results”?  Pretty sure that can’t be good for the manifesto.

So yeah, all I am trying to say is that documentation is communication. Not just to you, not just to other programmers, but to future programmers, users, and even a future version of yourself that you would treat better if you could see the pain in his (or hers) eyes. 

This one should be simple to anyone who sees it (once I decode what I mean by fundamentally… and sound…by then for sure!) In the initial post I defined this as – fundamental rules enforced such that you don’t have to check datatypes, base domains, relationships, etc.  The gist here is that you at a minimum don’t have to spend all of your time validating what has been saved.

As an analogy, consider the first car your mom and dad (or mom or dad or grandparent or even yourself, so as not to be offensive to anyone!) purchased. If it was like my first car, my father made sure that it was fundamentally a safe vehicle, but that was pretty much all you could say about it.  It didn’t have all of the bells and whistles (unlike my car now that has lots of whistles) but it had an engine that was solid and managed, a steering wheel that turned, and brakes that stopped the car.  Near the end of my ownership of the car, it had a set of pliers for a gear shift, but at that point the car was losing the fundamentally sound “zone”.

So why does this matter? There is a large number of non-db programmer types who would prefer it if the database was treated as nothing more than a data storage device with some search capabilities built in.  SQL Server is happy to serve in this capacity, but what a waste of resources.  Why?

• Data has to be validated to be used– ETL type processes are all to often ETCCCCCL processes, where instead of Extract, Transform, and Load, there is a bunch of Clean, Clean, Clean processes tossed in.  The problem is that no matter how good you are, without using at least foreign key constraints and transactions, you will end up with less than pleasant data once users and the reality of concurrent data access gets a hold of your data.
• Some forms of validation are very difficult to do outside of the data layer – starting with foreign key constraints, this list is actually more large than you might imagine.  For example, any validation where another table or multiple rows is involved, like a check constraint that check the cardinality of a relationship.  Another example is uniqueness. Uniqueness enforcement should always be done in a UNIQUE constraint. Worst case you are going to end up with a unique index that is very helpful to the query processor.
• Loading data is far safer – If you rely completely on a layer to manage the integrity of the data, what happens when the ETL layer is pointed AT your data?  Do you have to recode all of the rules?  Of do you just hope that the rules are all followed? Unfortunately hope springs eternal in a lot of situations where it was not such a dandy idea, and the UI starts failing because of unknown data rules.
• Data in a nullable column ought to allow nulls – Nothing quite cheeses my cracker more than trying to set up a scenario and I leave all data nullable and it crashes the app.  So you start filling in data as you figure it out (hopefully the app tells you what column it is stumbling on) until you finally get there. If the app requires data, then the database should too.

Look, In the early days of client-server programming, we tried using the data layer as the be-all and end-all layer of data programming, and it stank. Our UIs were as bad as a lot of web pages are now. No validation, just let a different layer handle it and report errors after the user had entered lots of data. I am not suggesting that.  And be clear about something else too.  This is NOT about stored procedures.  Stored procedures are part of the data access layer, not the data layer.  Whether or not you favor procs as a data access layer is immaterial to this discussion. I am talking about the fundamentals that can be done with or without a TSQL implemented data access layer.

For example:

• Theoretical data ranges – Salary greater than 0, Temperature for the weather forecast within a range that could actually occur, etc.
• Data types – don’t just use sql_variant for every column, leaving the user to deal with it at runtime. Pick the type that actually represents what you are modeling.
• Data Length – And in the same vein as data type, choose a length that is reasonable. Too many databases have varchar(50) for all string types, unless it is text.  Come on, think about it and set the max limit if there is one.  Don’t use an “unlimited” type and only give the user 50 characters.  And don’t do the opposite either.  Varchar(50) with 100 characters on the UI.  Unless your testers are excellent (or psychotic) you won’t find that out until much later
• Uniqueness – Already mentioned.  Don’t leave that to the UI, you are going to need an index in any case to do this well.
• Relationships – Foreign key constraints rule and take no thought.

And implement as much of this on the UI as needed also. Best case you generate the code from both from a rule repository. Don’t force the poor user to type data into 100 columns and then find out that the need another piece of information in a proper format, only have the form timed out when they come back to it.  Unless you hate your users, in which case you are excused as long as one of my friends isn’t stuck using your evil UI…then it just isn’t cool.

File this under the “I can’t believe there is still stuff that I keep learning about SQL Server 2005!” though thankfully most things I find I learn are things I wouldn’t be all that likely to use.

I was asked today how I felt about using the syntax:

GRANT EXECUTE TO [username]

to give users rights to all procedures in the database (and you could use it with SELECT, INSERT,…and other rights too).  Well, first off, I had to admit that I didn’t know you could do this. I knew you could grant execute rights to a user on a schema, but not the entire database. Jasper Smith had an article back in 2004 on sqldbatips that covered it (http://www.sqldbatips.com/showarticle.asp?ID=8), so it isn’t some big secret.

I often use the technique to grant a user all rights to a given schema:

GRANT EXECUTE on SCHEMA::schemaName TO [username]

I often use the this technique to apply execute rights to an application login/users to a given schema. I don’t like it as much when using the dbo schema, since it commonly contains other object that I don’t want to just give blanket rights to, but when using named schemas to segregate objects into functional groups (partially for security, and partly for logical separation) I feel it is a good idea.

Even using the dbo schema isn’t horrible, as long as you understand what you are doing and are careful to separate out other procedures. I am a big believer in having the database be as self contained as possible, so I try to put maintainence objects and such in the database, typically in a schema named utility. Often this might have a procedure to drop all foreign keys, or indexes, etc.  Whatever I might need during a data load operation, or even just general maintenance. So granting rights to the entire db seems a bit too lenient as I don’t want ANYONE who isn’t the dba running these procedures.

I guess the fact is that I think that the database security should be a bit more stringent than a simple GRANT all rights to EVERYTHING, as you have to be cognizant that sometimes there will be objects in the database that just shouldn’t be opened up for the programmer to accidentally use, thinking that an object does something different than it actually does.  When you carefully lay out schemas, odds are that you are considering the purpose/meaning of the schemas and have  a plan for the schemas which SHOULD consider security/usage as well.