Re: Migrating from 2 Instances to 1 Instance (SQL 2005)

At the very least, have you looked at sp_change_users_login? That should
help you to consolidate servers.


Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

"Jeff Gibson" <Jeff Gibson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
Hello, someone please help!!!!

I am trying to simplify life by combining 2 sql instances that are on the
same box into 1. Basically there is only 1 database per instance that is
being used for any real time production activity. My main problem is, that
there identical user logins (name and pw) setup on the two instances, and
when I try to combine them and call stored procs it can't find them in some
cases. In my research I found that this is because for the user, SQL really
only looks in the user's default database for the SPs. My problem with
is that I have different applications using the same login and it will be a
lot of trouble to change the user that the apps are using.

Also, it doesn't appear that the initial catalog param I am using in my
connection string is actually doing anything. Actually, I can completely
remove it and it makes no difference in how the application acts (mainly
because the connection just gets established to the default DB no matter
I pass in), any ideas on this? I mean, what is the point of having the
option if it doesn't do anything.

So, I guess my main question is, is there any way to make SQL look at more
than just the default DB to connect to, or is there a setting in SQL 2005
that allows me to connect to a different DB other than the default if I want
to by looking at the connection string.

Any help will be greatly appreciated...