How to grant database user read/write access roles the "new way"?

Question

OK, so I have a database, a login and a database user assigned to a login. To grant that user db_datareader and db_datawriter access to the database I would do this:

USE [mydatabase];
EXEC sp_addrolemember db_datareader, [myuser];
EXEC sp_addrolemember db_datawriter, [myuser];

But if I'm reading the documentation correctly, the use of sp_addrolemember is discouraged:

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.

So how do you do this with ALTER ROLE? When I do this:

ALTER ROLE sys.database_role_members.db_datareader ADD MEMBER [myuser];

I get this error:

Incorrect syntax near ADD MEMBER

Answer 1

If you are using sql server 2008 only sp_addrolemember will work.

The alter role syntax is only valid for the 2012 version. It is due to that you get an incorrect syntax error