Carter's Dad - Parker's Dad Too!

Here's one of those tricks that I'm sure I wouldn't need if I knew a bit more about what the heck I'm doing, but it helps me nonetheless....

A typical scenario for me, is that I get an assignment to work on a project for some new client, and I create the database on my laptop, and begin work.  Sooner or later they give me connection information for the "real" server where I will be deploying to.

That's when the fun begins :)  Out of habit, my local Enterprise Manager logs in to my local SQL Server (on the laptop) as SA.  I know, many of you will quit reading and yell at me just over this.  But, it's my laptop, not a production server.  It's easy....  So, I'm happily creating tables procs, etc.  Not that I do all of that in EM, but I get to Query Analyzer after going into EM via the pulldown menu, which nicely brings me to the same database I'm currently "in."  I'll bet lots of people do it that way.  This article is for them :)

So then, all of my objects are owned by dbo!  Then I need to deploy them to some remote server, where I can only login as a designated user, who perhaps is NOT dbo :)  This is where the problems begin.  Trying to re-create objects on the remote server via sql scripts is a major pain, because all of the tools for generating these scripts throw the owner in as well.  Once you are deployed and want to do a "diff" with any of the tools that do that, you are hosed as well for the same reason, your DIFF tool will find dbo.mytable as different from someuser.mytable.  This is the 9th circle of hell, I think.

So, the trick?  It's amazingly simple, and will probably make many of you stop reading my blog forever.  But hey, it works for me, so I thought it might work for someone else :)  Here it is:

1) Edit your hosts file on your development machine.  Set 127.0.0.1 for a hostname like sql.thisapp or sql.thatapp.  Anything like that.  Just make each entry unique.

2) In your Enterprise Manager, you can then add a new sql registration to your same localhost as many times as you'd like, with different names, like the above sql.thisapp or sql.thatapp.  Login to those connections with the name/password that you will be using on the remote server, instead of SA, or windows.

That's it.  So you can have your localhost sql server referenced many times in the same instance of EM, each logging in with a different username, having access to different databases, etc.  Of course, you still have the default (local) registration, where you login as SA, or using Windows, whatever, so you can manage the installation as needed.