How to: Add a User to a Role
You can add a user and a role to a database project by using Schema View. However, you must manually associate the user with the role in the post-deployment script. For more information about post-deployment scripts, see How to: Specify Pre-Deployment or Post-Deployment Scripts.
Important
You cannot add users to roles in a Data-tier Application (DAC) project because DAC projects do not support the EXEC StoredProcedure construct, except within the body of an object, such as a stored procedure or function. For more information, see the following page on the Microsoft Web site: Features Supported in Data-tier Applications.
To add a user, add a role, and associate the user with that role
Open or create a database project. For more information, see How to: Create Database and Server Projects or How to: Open a Database or Server Project.
If Schema View does not appear, open the View menu, and click Database Schema View.
If you have already created or imported the user, go to step 7.
In Schema View, right-click the Security folder, point to Add, and then click User.
The Add New Item dialog box appears.
In Name, type the name of the user whom you want to create.
Click Add.
The user is created and added to the database project. The file that contains the user definition appears in Solution Explorer. The database object for the user appears in Schema View.
If you have already created or imported the role, go to step 11.
In Schema View, right-click the Security folder, point to Add, and then click Database Role.
The Add New Item dialog box appears.
In Name, type the name of the role that you want to create.
Click Add.
The database role is created and added to the database project. The file that contains the database role definition appears in Solution Explorer. The database object for the role appears in Schema View.
In Solution Explorer, expand the Scripts node, expand the Post-Deployment node, and double-click Script.PostDeployment.sql.
The Transact-SQL editor opens and displays the post-deployment script.
At the bottom of the post-deployment script, add the following Transact-SQL statement:
EXEC sp_addrolemember N'RoleName', N'UserName'On the File menu, click Save Script.PostDeployment.sql.
In Solution Explorer, right-click the database project, and click Build.
Your database project is built, and the build script is created in the location that you specified in the database project properties. If you open the build script, the line that you added to the RoleMemberships.sql file appears in the post-deployment section of the build script.
The next time that you deploy the database project, the user and role will be created, and the user will be associated with the role.