CODEWITHSUNDEEP
sql-serversql-server-data-toolsdacpac
Vickey C
Vickey C

Reputation: 41

SSDT Unresolved Reference to Object

I'm receiving a number of "unresolved reference to object" errors on a SQL Server Database Project I created in VS 2017.

These errors all point to the "sys." objects.

I have added a .dacpac reference from the master database, including generating a new .dacpac for the master database.

I've also tried adding references to the master database itself, with no luck. I've also rebooted my machine, but that didn't help either.

I've tried the remedies listed in Stack Overflow, but none of those solutions have worked for me. Any suggestions? Here's an example of the errors I'm getting from the build:

SSISDB\internal\Views\current_user_object_permissions.sql(15,55): 
Error SQL71501: View: [internal].[current_user_object_permissions] contains an unresolved reference to an 
Either the object does not exist or the reference is ambiguous because 
it could refer to any of the following objects: [internal].[object_permissions].[pri]::
[name], [sys].[database_principals].[name] or [sys].[database_principals].[pri]::[name].

Updated

Sql:

CREATE VIEW [internal].[current_user_object_permissions] AS 
SELECT obj.[object_type], obj.[object_id], obj.[permission_type], obj.[sid], obj.[is_role], obj.[is_deny] 
FROM [internal].[object_permissions] AS obj 
INNER JOIN [sys].[database_principals] AS pri ON obj.[sid] = pri.[sid] 
WHERE ((pri.[type] = 'S' OR pri.[type] = 'U') AND obj.[sid] = USER_SID (DATABASE_PRINCIPAL_ID())) OR ((pri.[type] = 'G' OR pri.[type] = 'R') AND IS_MEMBER(pri.name)=1)

Upvotes: 4

Views: 2733

Answers (1)

Alexey Varentsov
Alexey Varentsov

Reputation: 386

There is an answer
1. Add Database reference to project 2. Select system database
https://dba.stackexchange.com/questions/40592/referencing-system-views-in-ssdt
3. Use master master.sys.database_principals in view

Upvotes: 2

Related Questions