Role membership with VS 2010 Database development tools

One question that arises when it comes to database deployment is how do we deal with role memberships. The sample answer is; create a file that ends with .RoleMembership.Sql and VS2010 will automatically use it’s content.

The easiest way to do it is to create the role in the database using sp_addrolemember and retrofit the added roles in the database project by using a schema compare feature. Simply specify database as source and db project as target and use the “write update” button to have the file created at project level.

This is not ideal since roles can differ from one server to another. In a future post,I’ll describe some solutions or ideas on how to handle this situation.

Christian