Problem with "CREATE LOGIN" for SQL Logins - a glitch??
-
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???
-
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
---------------------------------------------------------------------
-
Thanks - sometimes it's the simplest things you just can't find documented
-
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