Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    26

    Unanswered: alter user user_name with login= login_name

    Hi,


    I am try to run the following command on a 2005 Server

    use temp
    alter user [MY-LP79EFIWXO93\login_a]
    with login = [MY-LP79EFIWXO93\login_b]

    But keep getting this error

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

    I have search the MS SQL website and know the syntax exist, but can't get it to work. I have try to run

    use temp
    alter user 'MY-LP79EFIWXO93\login_a'
    with login = 'MY-LP79EFIWXO93\login_b'

    use temp
    alter user MY-LP79EFIWXO93\login_a
    with login = MY-LP79EFIWXO93\login_b

    use temp
    alter user [login_a]
    with login = [login_b]

    use temp
    alter user 'login_a'
    with login = 'login_b'

    use temp
    alter user [MY-LP79EFIWXO93\login_a]
    with login = [login_b]

    But still get the same error. However when I run the command

    use temp
    alter user [MY-LP79EFIWXO93\login_a]
    with name= [MY-LP79EFIWXO93\login_b]

    It run and changes the database user name, but I want to change the login name. Has anyone got this instruction to work properly, do I need to install a particular SQL Service Pack, do some kind of config on my server, run some kind of command before running this instruction?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In short, you already have gotten to work properly. You can not change the login for a user, but you can change the name of the user. From BOL:
    Code:
    ALTER USER user_name  
         WITH <set_item> [ ,...n ]
    
    <set_item> ::= 
         NAME = new_user_name 
         | DEFAULT_SCHEMA = schema_name
    In order to change the login associated with a user, you need to drop the user, then CREATE the user with the desired login name.

  3. #3
    Join Date
    Aug 2008
    Posts
    26
    Hi,

    I've looked at http://msdn.microsoft.com/en-us/library/ms176060.aspx and there is an instruction which is similar with the one you specified but with a
    LOGIN= login_name option..??

    Thanks.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Then this was probably added in a service pack. What do you get for this;
    Code:
    select @@version

  5. #5
    Join Date
    Aug 2008
    Posts
    26
    I am getting ..

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •