Blog Post

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.







You rated this post out of 5. Change rating




You rated this post out of 5. Change rating