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 Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data 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.