How do you add a User Created in a VS Database Project to a Role?

Question

In a VS 2010 Database Project I have created a user and a role and I want to add that user to the role. How do I do that?

I understand the SQL Script it takes to do that, I was hoping to find a way to do it without dropping it in the Post.Deployment. I think that will work but it seems wrong. I am looking for a best practice for VS 2010 Database Projects.

Answer 1

With the followin stored procedure:

EXEC sp_addrolemember 'Production', 'Mary5'

(Prodution is the role, Mary5 is the user)

Add this to a script in your project.

Personally I would create a folder under the Scripts folder called Authorization and create file for each role that contains the addrolemember for different users.

In your postdeployment you can then do something like :r ..\Authorization\Production.sql (the last bit is somewhat rusty so I'm not sure if I'm spot on there)

Answer 2

In the solution, if you expand the "Schema Objects" node there should be a file called *.rolemembership.sql (replace the asterik with the db project name).

You can edit this file adding the lines that rene mentioned (above).