Migrating from 2 Instances to 1 Instance (SQL 2005)

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 thi
s
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 wha
t
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...At the very least, have you looked at sp_change_users_login? That should
help you to consolidate servers.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Jeff Gibson" <Jeff Gibson@.discussions.microsoft.com> wrote in message
news:7BBD384B-A311-493B-B7F9-D66985374B2B@.microsoft.com...
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
this
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
what
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...|||Thanks for replying Tom,
As far as I know, our DBA has successfully migrated all the users, in fact,
I am having no login issues for the users themselves (since the logins are
instance based), only when the user tries to access resources that are not
located in its' default database. Basically, I have a "LoginUser" user on
two databases; say DB1, and DB2 of the same instance. If the stored
procedure is on DB2, but LoginUser's default database is DB1, I get a messag
e
from SQL saying that it couldn't find the SP. If I change the default db
using sp_defaultdb to DB2 then the call works, but this is not an option
since I do have stored procedures also on DB1 that the LoginUser is accessin
g
(well, needs to) on DB1 also. I can also use a three part naming convention
[DB2].[dbo].[StoredProcedureName], but for that to work I will n
eed to change
and re-compile all my applications - which in that case I could just add a
new user, say LoginUserDB2, and change only the applications' connection
strings that access resources on DB2 - but the problem with that is too much
up front work since I have many applications, some of which I am not even
sure where the source code is to actually change the string. Basically, I a
m
trying to find a quick and dirty way for a singe login user to access
resources on 2 databases of the same instance without changing any
application code to do so, if that is my only option I will probably just
keep the 2 instances and move on to other things.
The other weird thing is, since I am passing in the database name in my
connection string, why doesn't this signify the database to connect to. Wha
t
is the point if I can only access the default database resources?
Thanks again.
"Tom Moreau" wrote:

> At the very least, have you looked at sp_change_users_login? That should
> help you to consolidate servers.
> --
> Tom
>|||Hmm. I can't understand why it would ignore the database in the connect
string - unless somewhere in the app it's hard-coded to issue a USE
<database> command. Another explanation is that the connect string is
simply incorrect. It may not throw an error; rather it simply doesn't
switch to the specified DB and goes with the default. Can you post the
connect string (sans password)?
What method was used to migrate the logins, BTW? Have you looked at:
http://support.microsoft.com/defaul...kb;en-us;246133
http://forums.microsoft.com/MSDN/Sh...309521&SiteID=1
The scripts preserve the SID's. I'm not sure if that's what happened here.
That said, you may be able to assign default DB's to the new DB, based on
the old system, using SQL-DMO. Just step through the Logins collection of
the source server and look at the Database property. For the same Login on
the destination, set the Database property to be that of the source server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff Gibson" <JeffGibson@.discussions.microsoft.com> wrote in message
news:A1DCE3CB-AF90-47DB-A28D-AD209B3C157C@.microsoft.com...
Thanks for replying Tom,
As far as I know, our DBA has successfully migrated all the users, in fact,
I am having no login issues for the users themselves (since the logins are
instance based), only when the user tries to access resources that are not
located in its' default database. Basically, I have a "LoginUser" user on
two databases; say DB1, and DB2 of the same instance. If the stored
procedure is on DB2, but LoginUser's default database is DB1, I get a
message
from SQL saying that it couldn't find the SP. If I change the default db
using sp_defaultdb to DB2 then the call works, but this is not an option
since I do have stored procedures also on DB1 that the LoginUser is
accessing
(well, needs to) on DB1 also. I can also use a three part naming convention
[DB2].[dbo].[StoredProcedureName], but for that to work I will n
eed to
change
and re-compile all my applications - which in that case I could just add a
new user, say LoginUserDB2, and change only the applications' connection
strings that access resources on DB2 - but the problem with that is too much
up front work since I have many applications, some of which I am not even
sure where the source code is to actually change the string. Basically, I
am
trying to find a quick and dirty way for a singe login user to access
resources on 2 databases of the same instance without changing any
application code to do so, if that is my only option I will probably just
keep the 2 instances and move on to other things.
The other weird thing is, since I am passing in the database name in my
connection string, why doesn't this signify the database to connect to.
What
is the point if I can only access the default database resources?
Thanks again.
"Tom Moreau" wrote:

> At the very least, have you looked at sp_change_users_login? That should
> help you to consolidate servers.
> --
> Tom
>|||I'll reiterate - check out the sp_change_users_login. Your "identical"
user ids really aren't. They are from two different instances,
therefore have two different SIDS. unless your DBA removed all the
users and completely recreated them, they won't have access. Just
recently ran in to this, and that stored procedure saved the day.|||Hi
Tell your DBA to read this article
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148583484.249905.46360@.u72g2000cwu.googlegroups.com...
> I'll reiterate - check out the sp_change_users_login. Your "identical"
> user ids really aren't. They are from two different instances,
> therefore have two different SIDS. unless your DBA removed all the
> users and completely recreated them, they won't have access. Just
> recently ran in to this, and that stored procedure saved the day.
>