• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

SQL 2005 Syntax Error 'CREATE USER'

I'm trying to do something ultra-simple, yet SSMS keeps telling me there's a syntax error in this statement:

    CREATE USER oboTest2

I pulled it straight from an example in a Microsoft Tech Ed document. I had previously created a server login of the same name as the user, and was trying to create the database user of the same name. This first piece of code executed fine:

USE master
GO
EXEC sp_addlogin  oboTest1, pa$$word, myDatabase
EXEC sp_addlogin oboTest2, pa$$word, myDatabase
GO

but when I try to execute this:

USE myDatabase
GO
CREATE USER oboTest1
GO

SSMS insists:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'USER'.

According to what I've read, this should be a perfectly legit syntax for 'CREATE USER' . Any ideas?


0
CESD123
Asked:
CESD123
  • 3
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
create user wont work in sql 2000 or earlier versions
0
 
jorge_torizCommented:
CREATE USER MyTestUser FOR LOGIN MyTestUser

Map your database user to a valid login
0
 
jorge_torizCommented:
In your case could be CREATE USER oboTest1 FOR LOGIN oboTest1
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
CESD123Author Commented:
Hello aneeshattingal and jorge_toriz,

Thank you for your responses, but unfortunately they don't solve the problem. I had previously tried

     CREATE USER oboTest1 FOR LOGIN oboTest1

and found it returned the same error as "CREATE USER oboTest1" - remember, SQL is complaining about a syntax error - it's not even trying to execute the statement.

BTW, this is SQL 2005 (see title of question).

Very mysterious. Any other suggestions?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Very mysterious. Any other suggestions?
can you recheck the Compatability level of that database, right click on the database -> property -> Options
0
 
jorge_torizCommented:
I agree with the last comment of aneeshattingal, if the compatibility level is set to 80 your statements will be evaluated as they were in SQL 2000, then the runtime won't accept the CREATE USER statement.
0
 
CESD123Author Commented:

That makes sense, but the only options given for compatibility level are 80 and 70 - it doesn't offer an option for "neither". I would think the compatibility level setting would _allow_ for backwards compatibility but not enforce it against one's will.  If it IS getting in the way, is there some way in TSQL to say "ignore compatibility level"?

If there's no way around this, how did one say "create user" in SQL 2000?


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Use. Sp_adduser instead of the above for SQL 2000
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now