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???