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 |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2014-10-09 : 11:14:57
|
| Folks,I am migrating a few databases from one server (SQL2005) to another (SQL2005)... in total 8 databases. I've done it by restoring the (.bak) copies.. now in process of restoring/remapping/recreating the logins and users. Since by default server logins do not migrate, I've rescripted them from original server and have created these logins on the new servers (expected to have the same SIDs).There's a login "mik2008" which on the old server can access almost all databases but now unable to access a couple of databases on the new server. I've tried to execute the following command on the required databases of new server, but it returns errorALTER USER mik2008 WITH LOGIN = mik2008Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'LOGIN'.Any help/ideas what I am missing? Thanks!!CheersMIK |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-09 : 12:21:32
|
| Shouldn't it be ALTER USER...WITH NAME...Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2014-10-09 : 14:38:00
|
| I used it based on this article http://www.karaszi.com/sqlserver/info_moving_database.asp ... But thanks Tara I've fixed the issue with the help of sp_change_users_login sproc .. I am not sure what the issue was exactly but that user was defined as sysadmin and granting it back the sysadmin role... things started working as expected.CheersMIK |
|
|
|
|
|
|
restoring logins and users