A Little About Databases

One of the huge disadvantages of using Microsoft Products I find is that you tend to do things improperly! Let me share with you something I learnt today.

I have installed the SQL Server 2005 Express Edition as the default database to use with Visual Studio for my projects. Working in an Enterprise Software Services Firm I am aware that you have special roles for a database administrator. The DBA will never share the system admin or db admin login credentials with you. For your application to access the database in a safe manner a new user is generally created for you to use! At least in my limited experience that is what I have come to know!

So for my demo project I decided to create a new user and not use the sa login. I named this user dba. When deciding on what would be the default schema of this user I decided on db_owner to avoid any access issues later on. ( My database knowledge is limited to RDBMS concepts, writing Queries, Sp etc. I have never undergone any training or ventured on my own into what one would call the DBA's domain). Using this user I created the tables required for my application. The tables created appear as db_owner.assets.

Now I am using the same user to connect from my app to db. I had no issues in visually building the Connection String which I promptly stored in my web.config file. I had no issues in building TableAdapters with simple SELECT statements as well. However, no I decided to graduate to the next level and create my own Stored Procedures. I try to do this Visually once again but I get a failure message saying the schema dba does not exist or does not have sufficient permissions. OOPS!!

How is that possible? I made dba the db_owner. I check and surely dba still is db_owner. I wonder what could be wrong. Maybe I should try and create the stored procedure from Management Studio Express instead of Visual Studio. Fails again with the same message!

I examine the stored procedure to find "CREATE PROCEDURE dba.assets AS ...". Oh! the schema. When dba created the tables they where created as db_owner.assets. So I expanded the nodes in the Management Studio Express and went to schemas to find that indeed no schema named dba existed. So I created a schema named dba as I saw fit and then tried creating the stored procedure. Voila!

Microsoft assumes a lot of things when it Visually creates anything for you. It sure makes things a lot simple and easy to use but can lead to simple errors like this. If only such an error were to creep up in a production environment!!In this case Microsoft should have taken care that the default schema for dba was db_owner.But that is what software is all about I guess; finding new improvements from time to time.