2012 DDL for Roles


I’m extremely pleased that Microsoft are continuing their commitment to provide full DDL to preform TSQL operations that have previously been preformed using stored procedures.

The particular case that I came across today was regarding roles and the ALTER ROLE syntax.

Prior to SQL SERVER 2012, it has only been possible to change the ROLE name using the ALTER ROLE syntax, and adding / dropping role members has been accomplished with the sp_addrolemember and sp_droprolemember - both of which are flagged for removal in a later version of the product.

Now, database_principles can be added to database roles with the syntax

ALTER ROLE role_name ADD MEMBER [database_prnciple];

database_principles can be removed from database roles using the syntax

ALTER ROLE role_name DROP MEMBER [database_prnciple];

I’m a big fan of database roles in the product and I’m excited to now see the addition of user defined server roles as well.

Have a nice day.






Tags: , ,

About Martin Catherall

Martin Catherall is RockSolid SQL consultant, a SQL Server MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Martin is an SQL SERVER Database Administrator in the Christchurch Area and has been active in the local IT community for over 10 years. He likes learning interesting stuff about databases and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.