Walkthrough: Create and Deploy a Database Project that References Shared Logins

Article
02/04/2013

If multiple databases reference the same server objects, you can create a project for each database and a server project into which you import objects such as shared logins, keys, or custom error messages. In this walkthrough, you set up an isolated development environment for a database that references shared logins that you import into a server project. This environment is based on the database project that contains the definitions for all the objects that are in the production database.

This walkthrough illustrates the following tasks:

Setting Up the Shared Server Project
Creating the Composite Database Project
Make the Project Available to the Team (Optional)

Prerequisites

You must have installed Visual Studio Premium and have access to a copy of SQL Server from which you can import server objects. That copy of SQL Server must be running on a server onto which you can deploy the database project.

Setting Up the Shared Server Project

When you refer to shared logins in a development environment, those logins are usually defined in a server project that already exists and that the server administrator maintains.

To create the server project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
Under Installed Templates, expand the Database node, and then click SQL Server.

Note

If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.
In the list of templates, click SQL Server 2008 Server Project.
In Name, type SharedServerProject.

You do not need to change the values in Location and Solution Name.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.

An empty server project is added to your solution and appears in Solution Explorer.

Next, you import the server objects and settings.

To import the server objects and settings

In Solution Explorer, click the SharedServerProject project.
On the Project menu, click Import Objects and Settings.

Note

As an alternative to steps 1 and 2, you can right-click SharedServerProject and click Import Objects and Settings.
In the Import Database Wizard, specify a connection to the server from which you want to import objects and settings, and then click Start.

Important

For this walkthrough, you must specify the same server to which you will deploy your database project.

The objects and settings for the server are imported into your project.
Click Finish when the objects and settings have been imported.
On the View menu, click Database Schema View.
Expand the SharedServerProject node, expand the Server Level Objects node, expand the Security node, and expand the Logins node.
Identify and note one of the logins that appear for use later in this walkthrough.

Next, you build the server project.

To build the server project

On the Build menu, click Build solution.

When you build the solution, you create a SharedServerProject.dbschema file, which contains the definitions for the objects and settings in the server project. You will reference this file later in the walkthrough.

Next, you create a database project that uses a login that is defined in this server project.

Creating the Composite Database Project

To create the database project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
Under Installed Templates, expand the Database node, and then click SQL Server.

Note

If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.
In the list of templates, click SQL Server 2008 Database Project.
In Name, type CompositeDBProject.

You do not need to change the values in Location and Solution Name.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.

An empty database project is added to your solution and appears in Solution Explorer.

Next, you add a reference to the output from the server project that you defined earlier in this walkthrough.

To add a reference to the server project

In Solution Explorer, expand the node for the CompositeDBProject project, and click the References node.
On the Project menu, click Add Database Reference.

Note

As an alternative to steps 1 and 2, you can right-click the References node and click Add Database Reference.

The Add Database Reference dialog box appears. Because the solution contains only one database project, Database project schema (.dbschema) is selected.
Click Browse.

The Select Database File dialog box appears.
Specify the SharedServerProject.dbschema file that was created earlier in this walkthrough.

For example, you might specify the following location:

My Documents\Visual Studio 2010\Projects\SharedServerProject\SharedServerProject\sql\debug\SharedServerProject.dbschema
Click Open.

When you create a reference to a dbschema file created from a server project, by default the reference is set to use the literal "master" to refer to its objects. This is not a composite reference, because you have defined a database variable value. You only have to define a database variable if it represents a different database.
Click OK.

A reference is added to the server project. You can now use any objects that are defined in that server project as if they were defined in the current database project.

Next, you add a user object to the database project. The user object uses a login that is defined in the server project.

To add a user to the database project

On the View menu, click Database Schema View.
Expand the CompositeDBProject node, expand the Security node, right-click the Users node, point to Add, and click User.

The Add New Item dialog box appears.
In Name, type MyUser, and click Add.

The user is added to the database project and appears in Schema View. The Transact-SQL editor opens and displays the definition for the user.
In the Transact-SQL editor, update the user definition to match the following example:
```
CREATE USER [MyUser]
    FOR LOGIN [SharedLogin]
    WITH DEFAULT_SCHEMA = dbo;
```
Important

You must replace [SharedLogin] with the login that you identified in step 7 of the procedure that is titled "To import the server objects and settings".
On the File menu, click Save MyUser.user.sql.

Next, you define a database role.

To define a database role

In Schema View, expand the CompositeDBProject node, expand the Security node, and expand the Roles node.
Right-click the Database Roles node, point to Add, and click Database Role.

The Add New Item dialog box appears.
In Name, type DbAccounting, and click Add.

The role is added to the database project and appears in Schema View. The Transact-SQL editor opens and displays the definition for the role.
On the File menu, click Save DbAccounting.role.sql.

Next, you configure deployment properties for the database project.

To associate the user with the new database role

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 'DbAccounting','MyUser';
```
On the File menu, click Save Script.PostDeployment.sql.

Next, you configure deployment properties for the database project.

To configure the database project for deployment

In Solution Explorer, click CompositeDBProject (the database project).
On the Project menu, click CompositeDBProject Properties.

The properties for the project appear.
Click the Deploy tab.
In the Deploy Action list, click Create a deployment script (.sql) and deploy to database.
Under Target Database Settings, click Edit, and specify a connection to the server and database to which you want to deploy the database project.

Note

If you do not specify the same server from which you imported the server objects, deployment will fail because the logins are defined on that server.
On the File menu, click Save Selected Items.

Next, you build the database project.

To build the database project

On the Build menu, click Build Solution.

The project builds without errors.

Next, you deploy the database project to the target server and database.

To deploy the database project

On the Build menu, click Deploy CompositeDBProject.

The project deploys without errors.

Make the Project Available to the Team (Optional)

To add your project to version control

In Solution Explorer, click the node for the CompositeDBProject solution.
On the File menu, point to Source Control, and click Add Solution to Source Control.

Note

At this point, you interact with whatever version control software you have installed. This walkthrough provides steps to add your project to Visual Studio Team Foundation Server. If you have different version control software, you must substitute the equivalent steps.

If you are using Team Foundation Server, the Connect to Team Foundation Server dialog box appears.
In Connect to a Team Foundation Server, click the server that hosts the team project to which you want to add your solution.

Note

If you do not have a team project to which you can add the database project, see Planning and Tracking Projects.
In Team projects, click the team project to which you want to add the database project, and click OK.

The Add Solution CompositeDBProject to Source Control dialog box appears.
Click OK to accept the default values.

Your database project and the files that it contains are put under version control. Initially, they are still checked out. You must check them in before other team members can access them.
On the View menu, point to Other Windows, and click Pending Changes.

The Pending Changes window appears.
In Comment, type Initial database project creation.
On the toolbar for the Pending Changes window, click Check In.

The Check In Progress dialog box appears as the database project and the files that it contains are checked in. The icons in Solution Explorer change to show that the files are checked into version control.

Next Steps

With the database project checked in to version control, team members can continue to develop the database project. Database developers do not need to modify the server project. Therefore, you can restrict its permissions so that they can see but not change the shared objects.