Back when SQL Server 2005 came out there was a big change to the way that logins were handled. No more stored procedures to create a SQL login or to allow a Windows user or group to connect to SQL. It also brought the advent of allowing SQL logins to have the same kind of policy restrictions as you had in Active Directory.

Despite these huge changes for some reason Microsoft did not implement the same kind of changes when it came to roles. You still had to use stored procedures to grant role membership, this lead to an experience where you had to manage the syntax differently:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

USE tempdb;

CREATE USER [sirsqlDBAs];

CREATE ROLE RoleTest;

exec sp_addrolemember 'RoleTest', 'sirsqlDBAs';

exec sp_droprolemember 'RoleTest', 'sirsqlDBAs';

With SQL 2012 this old stored procedure way of managing things has finally gone by the wayside and we now manage roles in a similar fashion to logins:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

USE tempdb;

CREATE USER [sirsqlDBAs];

CREATE ROLE RoleTest;

ALTER ROLE RoleTest ADD MEMBER [sirsqlDBAs];

ALTER ROLE RoleTest DROP MEMBER [sirsqlDBAs];

This doesn’t just work with database roles. Also gone is the sp_addsrvrolemember syntax (which for some reason had you specify the login first then the role, opposite how sp_addrolemember did).

ALTER SERVER ROLE DiskAdmin ADD MEMBER [sirsqlDBAs];

ALTER SERVER ROLE DiskAdmin DROP MEMBER [sirsqlDBAs];

Managing roles this way makes a lot more sense than trying to do things via stored procedures. In an effort to ensure that old code still works Microsoft have left the old stored procedures in, but deprecated them, so don’t expect them to work in the next release. Anyway, wouldn’t you rather work to retool your code so that your security experience is more unified?