Post reply

SQL Server 2005 login error

  • September 18, 2009 at 6:43 am

    #138210

    I have SQL Server 2005. Let me explain what is wrong.

    I have to backup a database "XYZ" from server 1 and then restore that backup file as "ABC" in server2. Now that seems easy but i have created a maintenance plan for doing this. steps are shown below:-

    1) takes a backup of "XYZ" database from server1.

    2) then restores as "ABC" to server2.

    3) then creates users and logins to the "ABC" database that were there in "XYZ" database.

    everything worked fine until now but when i added a new user to the database "XYZ" i got an error in the create login part. see the error below:-

    sp_change_users_login @Action='update_one', @UserNamePattern='IBM\\DBtsmith', @LoginName='IBM\\DBtsmith'

    " failed with the following error: "Incorrect syntax near 'sp_change_users_login'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    i think some problems witht he orphand users but no idea still.please help.

  • September 18, 2009 at 7:10 am

    #1055567

    any help friends??????

  • September 18, 2009 at 7:21 am

    #1055578

    3) then creates users and logins to the "ABC" database that were there in "XYZ" database.

    You don't suppose to create the users, they have been moved with database already during the restore procedure.

    You don't suppose to create the logins, you just need to transfer them from one server to another.

    For SS2005 the login transfer script is here:

    http://support.microsoft.com/kb/918992/

  • September 18, 2009 at 7:26 am

    #1055585

    i have the script but all the user in the old database does transfer except this so have to issuse the last command for any orphand user but got error. any help

    or how about using

    exec sp_change_user_login ‘Auto_Fix’, ‘IBM\DBtsmith’

  • September 18, 2009 at 8:13 am

    #1055630

    I would manually delete this login on Server2 and re-apply transfer login script for this login only.

  • September 18, 2009 at 8:34 am

    #1055652

    sp_change_users_login does not work for Windows Logins. As suggested in the earlier posts Drop and recreate the user or you can use the below command.

    ALTER USER [username] WITH LOGIN = [windowsLogin]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply