Using master dbschema File To Resolve Login References

If you’re using MS SQL Server, Visual Studio’s database projects are a good way to manage your database objects.  After importing your database schema, if you working with existing databases, or creating from scratch, you will typically need a reference to the master.dbschema.  A typical error due to an unresolved server login reference will say “Error TSD03006: User: [someuser] has an unresolved reference to Login [someuser].”.

The schema file is just a xml file of the server object meta data, this is how it can resolve references when referenced by other database projects in your solution.  The VS Database Project comes with master.dbschema files that you can copy and modified if needed.  The different version of SQL server schema files can be found in “C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\”, if “C:\Program Files\” is your installed path of VS.  I would copy the master.dbschema file that you need to another location and rename with an appropriate name so others know what it represents.  If you have server logins that you don’t want to manage through you build and deploy, adding them to this copied master schema file is the way to resolve the build references.  Below shows the an example of the element that would need to be added to the dbschema file to resolve a particular login, the added part is italicized and bold.

<DataSchemaModel FileFormatVersion="1.0" SchemaVersion="1.0" DspName="Microsoft.Data.Schema.Sql.SqlDsp.Sql100DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False">
    <Header>
        <CustomData Category="ModelCapability">
            <Metadata Name="ModelCapability" Value="Default" />
        </CustomData>
        <CustomData Category="DBSchema">
            <Metadata Name="DatabaseType" Value="master" />
            <Metadata Name="SqlServerVersion" Value="10.00" />
            <Metadata Name="Author" Value="Microsoft Corp." />
        </CustomData>
        <CustomData Category="DatabaseVariableLiteralValue">
            <Metadata Name="Name" Value="master" />
        </CustomData>
    </Header>
    <Model>
        <Element Type="ISql90Login" Name="[someuser]">
            <Property Name="DefaultLanguage" Value="us_english" />
            <Property Name="IsCheckPolicyOn" Value="False" />
            <Annotation Type="GloballyScoped" />
        </Element>
    </Model>
</DataSchemaModel>

 

Once this is added, the login in reference error will resolve correctly.  Notice that I only have the one <Element> under the <Model> node.  If your projects don’t require usage of most of the master server objects, like extended procedures, you can removed them from the <Model> node.  Doing this trimming is worth the effort, especially in the cases where your using another projects dbschema file of which only a few items are needed to resolve references.  A good article of dbchema file trimming can be found here Right sizing the master.dbschema file for better design time performance.  Only including the necessary element in the model will reduce build time for projects depending on the schema file.