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

874 views Asked by At

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.

2

There are 2 answers

1
rene On

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)

0
P.McSwain On

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).