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 |
|
luthar28
Starting Member
3 Posts |
 Posted - 2010-05-01 : 11:36:51
|
| I just moved a SQL database from one server to another. I've restored it successfully except users don't have access to the database (after some investigation, I believe it's because force_Usa15 is an orphaned user)As well, the different servers have different user naming conventions. On the old one the user is 'force_Usa15' and on the current one its 'C271094_refgear'. The passwords are the same. All tables are prepended with force_Usa15 (so a table called cart would be called force_Usa15.cart). I tried using this: EXEC sp_change_users_login 'Auto_Fix', 'force_Usa15', 'C271094_refgear', 'password'But with no luck.Any ideas? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-01 : 12:17:07
|
If a login does not exist for the user then do this first:EXEC master.dbo.sp_addlogin 'UserName', 'Password', 'DefaultDatabaseName' Once the login exists you can connect the UserName, in the database, with the Login:USE MyDatabaseNameGOEXEC master.dbo.sp_change_users_login 'Update_One', 'UserName', 'UserName' |
 |
|
|
luthar28
Starting Member
3 Posts |
Posted - 2010-05-01 : 14:41:41
|
quote:
Originally posted by Kristen
If a login does not exist for the user then do this first:EXEC master.dbo.sp_addlogin 'UserName', 'Password', 'DefaultDatabaseName' Once the login exists you can connect the UserName, in the database, with the Login:USE MyDatabaseNameGOEXEC master.dbo.sp_change_users_login 'Update_One', 'UserName', 'UserName'
Of course, I just received an error:Msg 15247, Level 16, State 1, Line 1User does not have permission to perform this action.I'm assuming that means that I need to get someone on their side to do it? Also, the last statement: 'UserName', 'UserName'...Which username do I use? Do I do 'Force_Usa15'(old one), 'C271094_refgear' (new one)? Thanks for the help...I'm struggling! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-02 : 03:22:57
|
I didn't read your question carefully, sorry.Yes, you need someone with "more" permissions to create the Login ... BUT!:the bit I didn't read was that you want to connect an OldUserName from the old system to a NewLoginName [which presumably already exists] on the new system, the example I gave was for re-creating the same OldUserName on the new system.Although having said that your Auto_Fix probably should have worked (assuming you have enough permission).Syntax is (as per the DOCs "Books Online")sp_change_users_login 'Update_One' , 'OldUserName' , 'NewLoginName' -- This LoginName must already exist |
|
|
|
luthar28
Starting Member
3 Posts |
Posted - 2010-05-03 : 10:13:59
|
quote:
Originally posted by Kristen
I didn't read your question carefully, sorry.Yes, you need someone with "more" permissions to create the Login ... BUT!:the bit I didn't read was that you want to connect an OldUserName from the old system to a NewLoginName [which presumably already exists] on the new system, the example I gave was for re-creating the same OldUserName on the new system.Although having said that your Auto_Fix probably should have worked (assuming you have enough permission).Syntax is (as per the DOCs "Books Online")sp_change_users_login 'Update_One' , 'OldUserName' , 'NewLoginName' -- This LoginName must already exist
I did this: USE C271094_force_refgearGOsp_change_users_login 'Update_One' , 'force_Usa15' , 'C271094_refgear' And received this error: Msg 15063, Level 16, State 1, Procedure sp_change_users_login, Line 143The login already has an account under a different user name. |
|
|
|
|
|
|
|
|
Change Orphaned User