January 14, 2008 at 1:44 am
Hello,
I got a strange issue and I need some explanation:
I created a DB script using Management Studio 2005 (Highlight the DB --> right click --> Tasks --> Generate Script).
Then I run the script to create the same db on another Server and when the script finished I was not eble to log on again either Windows authentication and SQL server authentication.
I tried to reset pwd, to log on with SQLCMD, but it was not possible to log on.
What happen?
What I have to take into consideration before running a db script in another SQL server different from the original?
What action to take in the case it is not possible to log on again? I had to reinstall SQL Server 2005.
Thank
January 14, 2008 at 9:49 am
Hi,
what is the error message when you are trying to login?
also, to what server you can not login: the local one or the remote one?
Regards,Yelena Varsha
January 14, 2008 at 10:31 am
hello,
I connected remotely and locally.
The error is the typical error code when login/pw are wrong (at the moment the SQL server is already installed), but please, do tell me If I log on to server with administration right ad sa and others login exists and each pwd were right.
Thank
January 14, 2008 at 11:27 am
log on to server with administration right
Does this mean you are a member of builtin admins? You should be able to login in if you are.
Additionally, I have a few more questions:
Are all the SQL logins consistent (name and password) on both databases?
Did you script the users with the database?
Whenever I run into this problem, I drop/create the database logins in the scripted database because the UIDs do not match the new instance.
January 14, 2008 at 2:31 pm
You should script the logins with the same SID for another server. There are Microsoft articles on How to move SQL Server logins
Regards,Yelena Varsha
January 15, 2008 at 3:17 am
When I run the script on the first server, also the login were scripted.
But I do not understand why after I run the script on the second server I was not able to connect to SQL server, master and other db? (the script login is for database, it should not rewrite the sysadmin in the server).
Besides I could not drop / recreate login because I could not log in.
Thank
January 15, 2008 at 5:47 am
Did you try logging on using Windows Authentication?
-Roy
January 16, 2008 at 2:04 am
Yes, I used also Windows authentication (it is written in the first post).
Anyone can explain what happend, otherwise may be a big problem in the future running script
January 16, 2008 at 10:32 pm
The problem is the SID do not match in the restored database. You have to make the SID match in the master database or drop/create the database user logins.
January 17, 2008 at 2:17 am
Hello,
I understand the problem but what I miss is:
why I cannot access the entire SQL server? After the script ran, I cannot access neither SQL Server instance nor any databases. Only the Db restored should be the only one not accessible, should it?
Then you say: drop and recreate login, but If I cannot access to SQL server instance it means that I cannot do nothing, right?
Thank
January 17, 2008 at 4:07 am
as you said if the script is taken for a user db and run it should not replace other logins and db access.
Well try one thing use the same sa pwd that was used in the first server and try connecting the second one , so that if it has changed it should reflect and connect with the same pwd right?
Also what is the authentication mode you have in your second server ?
And is the second server SQL2005 ?
Murali.a
January 17, 2008 at 4:53 pm
January 22, 2008 at 1:52 am
Hello,
I would like to post it, but data are too sensitive to be shown.
In any case I would like to try it again and see what happen.
Thank
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply