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

sql, sql-server, sql-server-2008r2-express, t-sql

Solution

`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.

Problem

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?

Original source