Hinweis: Dieser Artikel wurde ursprünglich veröffentlicht in 2011. The troubleshooting steps apply to SQL Server 2008 through SQL Server 2022 and all versions of SSDT/SQL Database Projects. Syntax and approaches have been verified against current SQL Server documentation.

When importing or building a SQL Server Database Project (SSDT) in Visual Studio, you may encounter the following build error:

Error 1 SQL03006: User: [myUser] has an unresolved reference to Login [myLogin].

This error means the database project contains a CREATE USER statement that references a server-level LOGIN which does not exist within the project’s scope. Understanding why this happens and how to resolve it requires knowledge of SQL Server’s security architecture and how SSDT models database objects.

Understanding the Root Cause

SQL Server Sicherheit: Logins vs. Users

SQL Server separates authentication into two levels:

  • Logins are server-level principals. They live in the master database and control who can connect to the SQL Server instance.
  • Users are database-level principals. They live inside a specific database and are mapped to a Login. They control what a connected user can do within that database.

The relationship looks like this:

SQL Server Instance
├── Login: [myLogin]          (server level - master db)
│   ├── Mapped to: [myUser]   (in DatabaseA)
│   └── Mapped to: [myUser]   (in DatabaseB)
└── Login: [anotherLogin]
    └── Mapped to: [anotherUser] (in DatabaseA)

When you create a user in T-SQL, you reference a login:

CREATE USER [myUser] FOR LOGIN [myLogin];

Why SSDT Cannot Resolve the Login

A SQL Server Database Project in Visual Studio models only the database level. It generates a .dacpac file containing schemas, tables, stored procedures, views, and database users. However, Logins are server-level objects and are not part of the database model.

When SSDT encounters a CREATE USER ... FOR LOGIN [X] statement, it tries to validate that [X] exists somewhere in the project. Since the Login is not modeled, the build fails with SQL03006.

Lösungs

There are several approaches to resolve this error, each with different trade-offs.

Lösung 1: Remove Users from the Project and Use Post-Deployment Scripts

The simplest and most commonly recommended approach is to remove user definitions from the declarative schema and handle them in a post-deployment script instead.

Schritt 1: Delete or exclude the .sql file that contains the CREATE USER statement from the project (typically found under the Sicherheit folder).

Schritt 2: Add a post-deployment script (Script.PostDeployment.sql) that conditionally creates the user:

-- Post-Deployment Script: Create users if their logins exist
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'myLogin')
BEGIN
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'myUser')
    BEGIN
        CREATE USER [myUser] FOR LOGIN [myLogin];
    END
    -- Assign roles
    ALTER ROLE [db_datareader] ADD MEMBER [myUser];
    ALTER ROLE [db_datawriter] ADD MEMBER [myUser];
END
GO

This approach is robust because:

  • The build succeeds since there is no unresolvable reference.
  • The script checks for the login’s existence before creating the user.
  • It handles deployment to environments where different logins may exist.

Lösung 2: Add a Server Project (SSDT Server-Level Project)

If you want full declarative control over both server-level and database-level objects, you can add a SQL Server Database Project at the server level:

Schritt 1: In Visual Studio, add a new project to your solution:

  • Project Type: SQL Server Database Project
  • Target: Set the target to master or create it as a server-scoped project.

Schritt 2: Add the Login definition to the server project:

-- In the server project: Logins/myLogin.sql
CREATE LOGIN [myLogin]
    WITH PASSWORD = N'placeholder',
    DEFAULT_DATABASE = [master],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF;
GO

Schritt 3: Add a database reference from your database project to the server project:

  1. Right-click Referenzen in your database project.
  2. Select Add Database Reference.
  3. Choose Database project in the current solution and select your server project.
  4. Set the Database location to “Different database, same server” or use the appropriate option for server-level references.

After adding this reference, SSDT can resolve the Login and the build error disappears.

Caveat: This approach means Visual Studio now manages server-level objects. In many enterprise environments, server administration is handled separately, making this impractical.

Lösung 3: Suppress the Build Warning/Error

If you want a quick fix and understand the implications, you can suppress the SQL03006 error:

Option A: Project-level suppression

  1. Right-click the database project and select Properties.
  2. Go to the Build tab.
  3. In the Suppress T-SQL warnings field, add SQL03006.

Option B: File-level suppression

In the .sql file containing the user definition, add:

-- Suppress SQL03006 for this file
--SQLCMD

Or set the file’s Build Action to None in the Properties window so it is excluded from validation.

Lösung 4: Disable Schema Checking for Users

You can disable schema validation for specific object types:

  1. Go to Tools > Options > Database Tools > Schema Compare.
  2. Navigate to the SQL Server section, then the Object Type tab.
  3. Uncheck Users, Logins, or other security object types.

This prevents the schema comparison and build process from validating these objects.

Fixing Orphaned Users After Database Restore or Migration

The SQL03006 error in SSDT is closely related to a common production issue: orphaned database users. When you restore a database to a different server, the database users lose their mapping to server logins because the SIDs (Sicherheit Identifiers) no longer match.

Detecting Orphaned Users

-- Find orphaned users (users with no matching login)
SELECT
    dp.name AS [OrphanedUser],
    dp.sid AS [UserSID],
    dp.type_desc AS [UserType],
    dp.create_date
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
    ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U')      -- SQL users and Windows users
  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
  AND sp.sid IS NULL;

Fix with ALTER USER (SQL Server 2008+)

The modern approach is to use ALTER USER to remap the user to the correct login:

-- Remap a single user to an existing login
ALTER USER [myUser] WITH LOGIN = [myLogin];

If the login does not exist yet, create it first:

-- Create the login on the new server
CREATE LOGIN [myLogin] WITH PASSWORD = N'SecurePassword123!';
GO

-- Then remap the user
ALTER USER [myUser] WITH LOGIN = [myLogin];
GO

Fix with sp_change_users_login (Legacy)

For older SQL Server versions, you can use the legacy stored procedure:

-- Auto-fix: automatically maps user to a login with the same name
EXEC sp_change_users_login 'Auto_Fix', 'myUser';

-- Or explicitly map to a specific login
EXEC sp_change_users_login 'Update_One', 'myUser', 'myLogin';

-- Report all orphaned users
EXEC sp_change_users_login 'Report';

Hinweis: sp_change_users_login is deprecated as of SQL Server 2012. Microsoft recommends using ALTER USER instead.

Fix All Orphaned Users with a Script

To bulk-fix all orphaned users, you can generate and execute the necessary statements:

-- Generate ALTER USER statements for all orphaned users
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql +
    N'ALTER USER [' + dp.name + N'] WITH LOGIN = [' + dp.name + N'];' + CHAR(13)
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
INNER JOIN sys.server_principals sp2 ON dp.name = sp2.name  -- login with same name must exist
WHERE dp.type IN ('S', 'U')
  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
  AND sp.sid IS NULL;

PRINT @sql;
-- EXEC sp_executesql @sql;  -- Uncomment to execute

Contained Database Users: Avoiding the Problem Entirely

SQL Server 2012 and later support contained databases, where users authenticate directly at the database level without needing a server-level login:

-- Enable contained databases on the instance
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;
GO

-- Set the database to partial containment
ALTER DATABASE [MyDatabase] SET CONTAINMENT = PARTIAL;
GO

-- Create a contained user (no login needed)
CREATE USER [myUser] WITH PASSWORD = N'SecurePassword123!';
GO

Contained users:

  • Do not require a server-level Login.
  • Are fully portable when the database is moved or restored.
  • Will not cause the SQL03006 error in SSDT.

Bewährte Methoden

  1. Separate security management from schema management. Use post-deployment scripts for user and permission management rather than including them in the declarative schema model.
  2. Prefer ALTER USER over sp_change_users_login for fixing orphaned users, as the latter is deprecated.
  3. Consider contained database users for applications that need database portability across servers.
  4. Document your security model. When using post-deployment scripts, clearly comment which logins and roles are expected on each target environment.
  5. Test deployments against a staging environment before production, especially when security objects are involved.

Zusammenfassung

The SQL03006: User has an unresolved reference to Login error occurs because SQL Server Database Projects model only the database level and cannot see server-level Logins. The most practical fix is to move user creation into post-deployment scripts, though adding a server-level project or suppressing the error are also viable options. For production environments, understanding the Login-to-User mapping and how to fix orphaned users with ALTER USER is essential knowledge for any SQL Server administrator or developer.

Verwandte Artikel