Speaking as one who's normally on the developer side of this question, but also does some database development, I don't think ORM has to be as scary as it seems. The big thing is the attitude of the development group. If they want objects to rule the world and have no consideration for relational data constructs, then yes it could be scary.
OTOH, using the example you reference which is NHibernate/Hibernate, these products were developed to work with a very wide range of relational schemas and are very flexible. If your develop team is rational (their attitude, not the methodology), then they understand that the data must be stored relationally and therefore designing a solid relational store is incredibly important to the long term health and performance of their application. I'll be a lot of ORM failures come down to not properly considering this.
If you're developing from scratch, then you really shouldn't have a problem. At times ORM can be difficult to sit on an existing database that was designed without any consideration of OO concepts (in that scenario I'd highly recommend iBatis.NET, though N/Hibernate is very flexible), but if you are designing a database from scratch it's easy to design a database that is solid from a relational perspective as well as friendly to being translated into an OO world.
From my experience, NHibernate/Hibernate does not in any way prohibit the database from being well designed. You will run into certain things (typically minor) where designing the table a certain way will make the development team's life much easier because it flows more with the default N/Hibernate way of thinking, but remember that there are a lot of people at an enterprise level using this product and those people can't get away with bad relational designs. In fact, google just contributed a module to Hibernate to allow sharding so that tells you they are using Hibernate with some incredibly large datasets.
On nice thing about a good ORM is that the queries will be consistent. This should allow you to profile the app and index pretty effectively. In addition, Hibernate can log all the queries to show you what it's doing. What's nice about N/Hibernate also is that it's hql language basically lets the developers define a query using sql syntax but using their objects in the query. (A little like linq, but looks much more like real sql). Basically if you have a particularly inefficient query for some reason, you can use hql to express the query like sql thereby forcing the ORM to query they way you want to hit an index or something. Also, I'm assuming this has made it's way into NHibernate, but Hibernate has the ability to define a query and then run it so you can manually define your 1 or 2 odd queries or execute a stored proc to do some large data manipulation that is not as efficient in the app layer.
The other thing is the "one size fits all" mentality. ORM is excellent for more transactional applications, but does not always fit a datawarehouse type scenario. It might might sense to code 90% of the app using ORM and then use something else for reporting or warehousing. This would allow you to develop the tables differently as well, since reporting type tables are typically designed very differently from transactional ones. This is something where the dev team might need to give to understand that the reporting type data needs to be structured differently and they might need to write a little sql, using a reporting tool, or consider an ORM that is more flexible like iBatis.NET.
I think if the db architects and the development architects will work together and both agree to consider the other's needs, this won't be that big a deal. Developers need to realize at the end of the data they are acting on relational data even though that's being abstracted for them. Must the same as though they don't need to do memory management in .NET or Java, they still need to think about what their code is causing the runtime to do behind the scenes. Dba's need to understand the huge benefits that ORM offers the coders and work with them to model their data in a relationally efficient as well as ORM efficient (i.e. minimum amount of mapping) way. If both sides stay involved through the whole process then nobody gets blindsided. Also, if either side is willing to give on some things (for example, don't quote me on this but IIRC, Hibernate really likes Identity/sequence type primary keys over natural keys although it handles both) then the result can be positive for both sides.
I'll bet a lot of the "ORM is slow, Hibernate stinks" comments come from developers who never considered the relational impacts of their object design. Those same developers then produce things that make dbas thing "this ORM stuff is junk, I can't support a database like this." ORM is a huge help, but you still have to understand what your ORM is doing behind the curtain. If you do, you can get the code benefits and still have a nice, well performing database.
The SQL vs. stored procs argument is far too deep for me to get into, but I can say I used to be a stored procedure zealot, but over time I've softened up to using stored procs mostly only where manipulation on large sets of data was needed and it didn't make any sense to do any logic out of the database and allowing all other logic to live in the application. As time has gone by, that has ended up making a lot more sense to me. That's just my opinion though, I don't want to hijack your thread into a dynamic sql vs stored procs flame.
I also don't see how any decent ORM would require dbo privileges much less sa. Sa would be insane. I'm assuming many just give dbo to the user because that's an easy way to make sure it has access to all objects in the database. I know to some dbas table access is scary, but that seems a little draconian to me. The application is going to get at the data one way or another. Creating walls in front of the data is not always justifiable IMHO. One nice thing is that when you are using an ORM that you are comfortable with you at least know the tool is generating reasonable sql rather than having a developer code everything hit or miss by hand.
Anyway, hope my ramble was of some value.