On SQL Server 2012 and 2014 Use ALTER ROLE ADD MEMBER instead of sp_addrolemember

While creating some scripts in a test environment to create logins and add members I noticed that ALTER ROLE has been upgrade with the ADD MEMBER option. this option replaces the stored procedure sp_addrolemember used in SQL 2008 R2 and previous versions.

The syntax for ALTER ROLE has now been upgraded with the ADD MEMBER option:

ALTER ROLE role_name

{

      [ ADD MEMBER database_principal ]

    | [ DROP MEMBER database_principal ]

    | WITH NAME = new_name

}

For instance on a SQL Server 2008 R2 granting logins to a Database would be like this:

USE [AdventureWorks]

GO

CREATE USER [Mydomain\MyAccount] FOR LOGIN [Mydomain\MyAccount]

GO

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'Mydomain\MyAccount'

GO

With SQL 2012 or 2014 you can now create logins with the following script:

USE [AdventureWorks]

GO

CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]

GO

ALTER ROLE [db_datareader] ADD MEMBER [MyDomain\MyAccount]

GO

On the MSDN library site the SQL Server 2012 documentation for sp_addrolemember warns that this will be phased out in a future version