VS2013 Database Project using SQL CLR - unresolved reference to object [servername\username] (the Database Owner)

Question

I am working on a Database project in VS2013 that contains a C# SQL CLR stored procedure.

The assembly owner is [servername\User123]
The database owner is also [servername\User123] (Being the database owner, this user obviously already exists on the server. I managed a successful publish of the database by commenting out the code in my SQL CLR procedure and publishing, the following occurs after I un-comment-out that code.)

If I try to build the project in VS, I get the error:

Error   1   SQL71501: Assembly: [DatabaseProject] has an unresolved reference to object [servername\User123].

...caused by:

CREATE ASSEMBLY [DatabaseProject] AUTHORIZATION [servername\User123]
FROM 0x4D5A90000300000004000000FFFF0000B8000etcetcetcetc
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

So, if in VS I add a create user script in the \Security folder:

CREATE USER [servername\User123] FOR LOGIN [servername\User123] WITH DEFAULT_SCHEMA = dbo
GO
GRANT CONNECT TO [servername\User123]

Then I can build the project.

However, if I then try to Publish the database, I get the error:

Creating [servername\User123]...
(47,1): SQL72014: .Net SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.
(47,0): SQL72045: Script execution error.  The executed script:
CREATE USER [servername\User123] FOR LOGIN [servername\User123];
An error occurred while the batch was being executed.

Is there a "right" way to do this?

Solomon Rutzky · Accepted Answer · 2015-07-08 17:16:08Z

This issue is due to a simple misunderstanding of how SQL Server handles the "owner" of the database as a database "principal". The concept of "database owner" shows up in two different ways:

As a database-level User having the name of dbo and a principal_id of 1. Now, what server-level Login maps to this particular User (i.e. "principal") is simply a matter of the value in the SID field in sys.database_principals:
```
SELECT sdp.*
FROM   [sys].[database_principals] sdp
WHERE  sdp.[principal_id] = 1;
```
This field will map to the SID field in sys.server_principals:
```
SELECT sdp.*, '---' AS [---], ssp.*
FROM   [sys].[database_principals] sdp
INNER JOIN [sys].[server_principals] ssp
        ON ssp.[sid] = sdp.[sid]
WHERE  sdp.[principal_id] = 1;
```
Pay attention to the following fields in sys.database_principals:
- type
- type_desc
- authentication_type
- authentication_type_desc
Seeing as how your database is currently owned by a Windows Login, run the query immediately above (the 2nd query--the one with both tables). Next, change the database to be owned by sa. Next, open up a NEW query tab. Finally, run the same query in this new window (so that you can easily compare the "before" and "after" results), making sure to pay close attention to the 4 fields noted above, as well as the sid field. (Please note that it is possible to create Users without Logins and those will not show up, obviously, in a query that JOINs to sys.server_principals, but this question is dealing specifically with Logins).

From all of this you should see that specifying a Login to be the "owner" of a database does not, in fact, create a User for that Login: it merely updates the definition of the dbo User. Hence, the Login that owns the database does technically exist in that database, but always with the name of dbo instead of its actual name. This is why you are getting the following error:

The login already has an account under a different user name.
As a Database Role that any User in the database can be added to in order to be given those permissions.

To fix:

Remove your CREATE USER script from the \Security folder.
Use AUTHORIZATION [dbo] instead.

Collectives™ on Stack Overflow

VS2013 Database Project using SQL CLR - unresolved reference to object [servername\username] (the Database Owner)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related