When Alter role i am getting incorrect syntax near the keyword 'ADD' on SQL 2008r2


Can you please let me know what this command doesn't work in  SQL 2008r2 but it does work in SQL 2012?

Error: Incorrect syntax near the keyword 'ADD'.

How I scan fix this so it would run in SQL 2008r2 ?

use [TESTDB]

CREATE USER [mdy\SF Tech] FOR LOGIN [mdy\SF Tech]
EXEC sp_addrolemember N'db_datareader', N'mdy\SF Tech'
ALTER ROLE db_owner ADD MEMBER [mdy\SF Tech]

-- alter authorization on SCHEMA::[ABOr.AB] to dbo
-- drop all local users
drop user [mdy\bag]
drop user [mdy\Bal]
drop user [mdy\Bhar]
Who is Participating?
Vitor MontalvãoMSSQL Senior EngineerCommented:
For SQL Server 2008 use the sp_addrolemember:
sp_addrolemember 'db_owner', '[mdy\SF Tech]'  

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Per the respective MSDN article the ADD and DROP keywords are only valid from SQL Server 2012 onwards.
michalek19Author Commented:
Thank you
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.