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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy