Jon Baker's Outer Join

One of the issues I had with SQL Server 2000 and predecessors is around security.  Not so much patching or even poor password policies.  I'm referring to granting rights to perform specific tasks in databases without giving away the keys to the kingdom.  There was very little granularity.  Let's say I want to give a data modeler the ability to create tables and indexes and all the things that go with that in a database.  I could give them DDL Admin, but that had rights I didn't want to grant to them, like create Triggers or SPs for example.  Another common scenario is to grant developers CREATE PROCEDURE rights in a development database, but not letting them create tables or indexes (I mean, really, that's not what developers should do, right!).  I could give them db_owner role and let them have at it, but that was WAY too much freedom (I recall a developer dropping the VEHICLE table when I consulted to a captive automotive finance company.  You can only imagine how that affected development the rest of the day). 

For it seems like years now, I have been hearing how SQL Server 2005 schemas would fix this problem.  I've heard it so much, that I took it for granted and even told other people this myth.  To those of you I've mislead, I'm sorry.  It's not true. Now that I have that off my chest, let's move on to specifics of how we can get closer to what we want, but not all the way. 

OK, schemas are cool and we should all be looking at how we can be using them, but they are not a security panacea.  Yes, you can grant rights on a schema (exec, select, delete...), which is helpful.  You can even assign a default schema to a USER, but not a Windows group.  That one still baffles me, and I'd love someone to explain it to me.  You can grant ALTER, just on a schema, which gets us a little closer to the goals we had above.  You can even go so far as to GRANT ALTER ANY, which means to you can give rights to change objects in any schema in the database.  Still this does not get us to the goal we set out to achieve.  If I GRANT CREATE PROCEDURE and ALTER on the DBO schema for example, a developer can create stored procedures, but s/he can also change the structure of a table or add indexes to the table.  DENYing CREATE TABLE does not take away the permissions either.  So with ALTER schema and CREATE PROCEDURE, the developer would not be able to CREATE tables or other objects in the schema, just procedures, but they could alter any object in the schema. 

So while I can limit which securables a security principal can act on by limiting which schema the principal can work with, I still can't totally limit the principal to only the role it is designed for.  It seems we have taken a baby step closer, but we are not completely there, yet.  Maybe Microsoft will fix it in a Service Pack?  Here' hoping.

I started this post by saying that granularity would be helpful. Some might think that granularity would make security completely unmanageable.  I think I'd like to make that determination myself.  It's my rope and my tree, so to speak.  I think I can choose to make a nuse or a rope swing.  We still have the predefined database roles that we can use, and we can create new roles if we need them to group the individual rights together.  I just think it would be nice to have the freedom to configure it however I need.

What do you think?  Do you use schemas?  How are you using them?  Am I way off and just haven't figured it out yet? 

So rumor has it that there is a cool data modeling tool in VSTS.  I haven't actually seen it yet, but I will tomorrow night at the Central Ohio SQL Server SIG as Corie Curcillo is presenting on it.  See here for details.  BTW...Geek Dinner @ Hoggy's Polaris (3 little pigs rock!).

I wonder however, how many DBAs are doing data modeling these days, and which tools they are using to do it. I have done a good bit of modeling over the last few years using mostly ERWIN (yuk!).  I'd love to hear about other's experiences.  When I heard Microsoft was getting into the modeling game a bit, I was surprised.  There are a lot of tools out there already (none of which are very good mind you).  But then I thought, "gee wouldn't it be great if I didn't have to leave the VS IDE to do a large piece of my work?"  Then I kinda got excited about this new tool.

I'll give a bit of a review of the tool after I've seen it tomorrow.  If you are interested in the topic, stop by the SIG.  We'd love to have you.  We are also getting a presentation on practical uses of Analysis Services by Dave Wichert (live meeting).  Should be pretty good.  Hope to see you there.  No swag list yet.  I'll have to climb into the prize box and see what I come up with.  There are a few options, like t-shirts, books, etc.  We'll see what falls on my head tonight.

Jon