How to join a database user to the db_owner role using a T-SQL script?

Question

I use SQL SERVER 2008 R2 Express.

I'm on a NEW project with a database named myDatabase with the db owner username myUsernamae.

I would like to use ALTER ROLE instead of sp_addrolemember due to this message from Microsoft regarding sp_addrolemember (Transact-SQL):

Important This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead.

I tried

USE [myDatabase]
GO

ALTER ROLE [db_owner] ADD MEMBER [myUsername]
GO

but I keep getting this error message:

Incorrect syntax near the keyword 'ADD'.

What is the correct syntax?

Answer 1

ALTER ROLE is new to SQL Server 2012, while you are using SQL Server 2008 R2.

I wouldn't worry to much about sp_addrolemember. Scripts generated by 2008R2 Management Studio use this procedure, so it should be a while before they really phase it out.