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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 restoring logins and users

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 error

ALTER USER mik2008 WITH LOGIN = mik2008

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'LOGIN'.


Any help/ideas what I am missing?

Thanks!!

Cheers
MIK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-09 : 12:21:32
Shouldn't it be ALTER USER...WITH NAME...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.

Cheers
MIK
Go to Top of Page
   

- Advertisement -