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?


CESD123Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
Use. Sp_adduser instead of the above for SQL 2000
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
create user wont work in sql 2000 or earlier versions
0
 
jorge_torizResearch & Development ManagerCommented:
CREATE USER MyTestUser FOR LOGIN MyTestUser

Map your database user to a valid login
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
jorge_torizResearch & Development ManagerCommented:
In your case could be CREATE USER oboTest1 FOR LOGIN oboTest1
0
 
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_torizResearch & Development ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.