 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / May 2006Migrating 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 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... At the very least, have you looked at sp_change_users_login? That should help you to consolidate servers.  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada 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 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 need 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. > At the very least, have you looked at sp_change_users_login? That should > help you to consolidate servers. 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/default.aspx?scid=kb;en-us;246133 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=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. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada . 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 need 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. 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/2006/04/03/567680.aspx > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.