Problem with "CREATE LOGIN" for SQL Logins - a glitch??

  • P Jones

    SSChampion

    Points: 12323

    We are currently porting from 2000 to 2005 and have a number of bought-in packages to move which require SQL logins. Our user accounts all are based on our job numbers so the logins have user names such as 00048594 which is fine if I create logins through management studio.

    However if I use a script the line

    CREATE LOGIN 00048594 WITH PASSWORD = 'PASSWORD',DEFAULT_DATABASE = master, CHECK_POLICY = OFF

    gives the error "Incorrect syntax near 00048594"

    If however I use

    CREATE LOGIN A00048594 WITH PASSWORD = 'PASSWORD',DEFAULT_DATABASE = master, CHECK_POLICY = OFF

    the account is created and I can rename the account in Management studio to drop the A. Thus it just doesn't like a numeric first character to the username.

    Using a variable and string concatenation (which is how I'd actually do it from the users in the database) gives exactly the same problem of course

    set @STR = 'CREATE LOGIN ' + @usern + ' WITH PASSWORD = ...... etc

    exec (@str)

    .

    is this a SQL glitch or is there a way around it - I really want to create these accounts with scripting as there are a lot to do???

  • george sibbald

    SSC Guru

    Points: 104200

    when you script out the logins, place [] around the login name. i.e.

    CREATE LOGIN [00048594] WITH PASSWORD = 'PASSWORD',DEFAULT_DATABASE = master, CHECK_POLICY = OFF

    if you use the 2005 version of sp_help_revlogin, it does this for you

    ---------------------------------------------------------------------

  • P Jones

    SSChampion

    Points: 12323

    Thanks - sometimes it's the simplest things you just can't find documented 🙂

  • Neel Singh

    SSC Veteran

    Points: 297

    how would i use my username and password as a variable for the above script?

Viewing 4 posts - 1 through 4 (of 4 total)

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