Visual Studio 2010 - Database Project with imported database fails to build

Question

I'm looking at the Database Project in VS2010, the idea being that I want something I can use to keep track of the database schema (in source control) and the ability to generate "new install" scripts, and change scripts.

When I create a new database project wizard and import my existing database schema, it won't "build". I get the error:

SQL03006: User: [scanner] has an unresolved reference to Login [scanner].

The SQL that generates this error:

CREATE USER [scanner] FOR LOGIN [scanner];

The user "scanner" is a login defined in the database I imported. I have no idea what it's teling me, and google isn't throwing much up. Any ideas?

Solution 1

The Login is actually defined in the master database of the server install. The CREATE USER statement needs to point at an existing Login otherwise it errors. The Database Project is not aware of the Login at the server level. Either you can create a Server Project to handle the Logins, or you can turn off the checking of Security objects in your Database Project. See the answer by Demetri M for more details: http://social.msdn.microsoft.com/Forums/eu/vstsdb/thread/1f8226fe-b791-4344-8735-3d38307e8664

Solution 2

I'm using Visual Studio 2012 for a SQL Server 2008 R2 database project. The options listed by @Judah don't appear to work anymore.

They now appear to be Settings that you configure while doing a Schema Compare:

Right click database project > Choose 'Schema Compare' > Choose the 'Settings' gear icon > Choose the 'Object Types' tab >

Logins are Non-Application-scoped. Database roles, Permissions, Role Memberships, and Users are all Application-scoped.

Unfortunately, the only way that I can find to preserve these is to save the schema compare. This can be a little inconvenient if you're sharing this on a team and would like project/database (or server) settings for any schema compare.

It gets the job done, though.

Solution 3

You can change the create user scripts to create roles. So instead of "Create user userName for login loginName;"

use "Create Role [userName] authorization dbo;"

This is a hack, but as long as you aren't having to deal with users and roles in your project, you can happily do the following:

GRANT EXECUTE ON OBJECT::[dbo].[sp_name] TO [userName];

Solution 4

Apparently, this issue is still occurring on VS2017 database projects as well.

I've managed to solve it by first creating the login and then create the user.

    -- Windows Account
    CREATE LOGIN [Domain\Username]
    FROM WINDOWS WITH DEFAULT_LANGUAGE = [us_english];

    GO

    CREATE USER [Domain\Username] FOR LOGIN [Domain\Username];
    GO

    -- Sql Acccount

    CREATE LOGIN [sql_account] WITH PASSWORD = 'Ch@ngeth1spA$swurD'
    GO

    CREATE USER [sql_account]
    FROM LOGIN [sql_account]
    WITH DEFAULT_SCHEMA = dbo

    GO



    -- Then set the sql file Build Action to "Build"

View more solutions