Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-28 : 04:44:38
|
I'm moving a database to a new serverI tried to script the logins but they are not workingI see the users and modified the passwords but i'm getting an error[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'xuser'.any adviseI tried to drop and recreate but when I try dropping in the database itself I get an error DROP USER [xuser]GOMsg 15138, Level 16, State 1, Line 2The database principal owns a schema in the database, and cannot be dropped.I moved the database and then the users should I do the opposite - delete the datbase and then move users and then database? |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 09:22:56
|
You don't need to drop database. After you migrate DB,you can either create logins with same username and password and map the logins or if you are doing for muliple dbs and logins then i would suggest you to search for "Transfer logins from one server to other server" kb article |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-29 : 11:59:44
|
this didn't do the trickdropping the database didn't work eitherI would like to delete from both and start again but it doesn't let me drop the user from the database (that was restored from the backup) what else can I do? |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-12-30 : 10:24:54
|
To migrate logins - lookup sp_help_revlogin and install that on the source system. Use that procedure to generate the script to create the logins on the destination.This script will create the logins with the appropriate SID, which will then map to the users in the database correctly and also creates the logins with the same password as on the source system. |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-30 : 13:42:56
|
thanks the actual problem wasn't with the logins at all - - I didn't have sql authentication checked on the new server |
|
|
|
|
|