Share via

How to: Add a User to a Role

  • Article

You can add a user and a role to a database project by using Schema View. However, in Team Edition for Database Professionals for Visual Studio 2005, 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.

To add a user, add a role, and associate the user with that role

  1. Open or create a database project. For more information, see How to: Create Database Projects or How to: Open a Database Project.

  2. If Schema View does not appear, open the View menu, and click Schema View.

  3. If you have already created or imported the user, go to step 7.

  4. In Schema View, right-click the Security folder, point to Add, and click User.

    The Add New Item dialog box appears.

  5. In Name, type the name of the user whom you want to create.

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

  7. If you have already created or imported the role, go to step 11.

  8. In Schema View, right-click the Security folder, point to Add, and click Database Role.

    The Add New Item dialog box appears.

  9. In Name, type the name of the role that you want to create.

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

  11. In Solution Explorer, expand the Scripts folder, expand the Post-Deployment sub-folder, and double-click the RoleMemberships.sql file.

    The RoleMemberships.sql file appears in the Transact-SQL (T-SQL) editor. If you have not previously associated users with roles or imported role members from an existing database, the file is empty.

  12. Add a line to the RoleMemberships.sql file to associate the user with the role, as in the following example:

    EXEC sp_addrolemember N'RoleName', N'UserName'

  13. On the File menu, click Save RoleMemberships.sql.

    The changes that you made to the RoleMemberships.sql post-deployment script are saved.

  14. In Solution Explorer, double-click the Script.PostDeployment.sql file.

    The post-deployment script appears in the T-SQL editor.

  15. If the post-deployment script does not contain the following line, add it at the end of the file:

    :r ./RoleMemberships.sql

  16. On the File menu, click Save Script.PostDeployment.sql.

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

See Also

Concepts

Working with Database Objects
An Overview of Database Scripts
Terminology Overview of Team Edition for Database Professionals