Friday, November 07, 2014

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

No comments: