July 22, 2008 at 5:28 am
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???
July 22, 2008 at 5:54 am
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
---------------------------------------------------------------------
July 22, 2008 at 7:08 am
Thanks - sometimes it's the simplest things you just can't find documented 🙂
October 14, 2008 at 6:11 am
how would i use my username and password as a variable for the above script?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply