Solved

Schema Not Showing in SSDT Database Project

Posted on 2016-09-14
26
59 Views
Last Modified: 2016-10-05
Hi guys,

I am creating a SSDT Database project. In SQL Server i have got different schema defined. However all those schema are not visible in SSDT. When i try to Create table like [Staging].[AddisonLeeAirportLookUp], it comes up with error message
Error	1	SQL71501: Table: [Staging].[AddisonLeeAirportLookUp] has an unresolved reference to Schema [Staging].	C:\Users\ali.shah\documents\visual studio 2013\Projects\Technical.Reporting.LegacyDataWarehouse\Technical.Reporting.LegacyDataWarehouse\Staging\AddisonLeeAirportLookUp.sql	1	14	Technical.Reporting.LegacyDataWarehouseStagingDB

Open in new window


However with dbo schema everything works. but i need the staging schema.

regards
0
Comment
Question by:shah36
  • 11
  • 8
  • 4
  • +1
26 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41798072
Does the login you're using have permissions for the Staging schema?
0
 

Author Comment

by:shah36
ID: 41798089
Yes i can use this Schema in SSMS. I have logged in with same credentials in SSDT as of SSMS
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41798142
change authorization and then try.
0
 

Author Comment

by:shah36
ID: 41798158
Hi Pawan,
Where do i need to change authorization?

regards
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41798177
Sample..

ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;    
GO
0
 

Author Comment

by:shah36
ID: 41799474
I have tried the following statement
ALTER AUTHORIZATION ON LegacyReportingDatabase_New.Staging TO dbo

Open in new window


but get the following error message
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'Staging', because it does not exist or you do not have permission.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41799481
Close the SSMS and try again.
0
 

Author Comment

by:shah36
ID: 41799484
same error again. i tried running it on Master database too
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41799637
Try to use sp_changedbowner stored procedure.
0
 

Author Comment

by:shah36
ID: 41799941
Well i might have done it wrong but here what i did
EXEC sp_changedbowner 'Staging'

Open in new window

and the error i got is
Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'Staging', because it does not exist or you do not have permission.

Open in new window

0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41827863
shah36, do you have this sorted out or do you still need help on this question?

For what I see, you need to provide us some more information.
What's the login that you're using in SSDTand and how are you trying to create the table?
0
 

Author Comment

by:shah36
ID: 41829553
Hi Vitor,

Thanks a lot for your reply. However when i did not get a response i used a separate database for staging instead of creating a schema.

But it would still be good to sort this out.
The login i am using in SSDT as the same as SQL Server (SQL Server authentication). And i am using ADD-Table from the context menu in SSDT

regards
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41829564
In SQL Server i have got different schema defined. However all those schema are not visible in SSDT.
Can you post a screen shot of your SQL Server Management Studio (SSMS) with all the existing schemas for the database?
0
 

Author Comment

by:shah36
ID: 41829579
Here is the picture Schemas However I think i missed a very important point.
in SSDT there is no connection reference / property. And it connects to the database through a publish profile that is when we publish it. Which means SSDT doesn't know any other schema apart from the dbo.
And the build fails when i try to use any other schema and publish wouldn't work.
You might come up with solution but i think this is the bug in SSDT.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41829601
I never worked with SSDT but trying to understand on the database point of view but by the previous comments you said that you could login with SSDT in the database via SSMS and that you could create a table in the Staging schema, right?
0
 

Author Comment

by:shah36
ID: 41829613
No sorry my mistake perhaps or it was off from my head.
SSDT works independently of SSMS.
in SSDT we create a SQL Server Project and there is no connection manager in SSDT unlike SSIS or SSAS.
We can create tables, stored procedures, UDF etc. in SSDT. But the only time we connect with the database is when we create a publish profile. And the publish profile contains the connection string. We can have many publish profiles with different connection strings which will publish the same database project to the different environments (dev, QA, production, local) this is an advantage.
But the disadvantage seems to be as there is no connection manager so i think SSDT assumes that there is only one schema (dbo) and if we try create the table like Staging.TableName it doesn't recognize it. Because of this the project fails to build and hence we can't publish with any other schema apart from dbo.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41829620
Oh, so it may not even be a bug but a limitation of the SSDT.
0
 

Author Comment

by:shah36
ID: 41829622
Yes you are right. So should i just close the question
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41829631
Yes, maybe is better to close it unless you have some kind of hope that somebody else can help you with this.
1
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41829674
I was reading more about SSDT (sorry can't test it here) and found this article where has an section about the schemas:
Default Schema

If you follow the premise that “anything that SQL Server picks as the default is probably wrong” then you should probably think about changing the default schema which, by default, will be [dbo]. SSDT will create all new objects in the default schema (unless otherwise specified).

This doesn't really says that you can't work with other schemas but you can work only with a default schema (it makes sense since in SQL Server a database user will have always a default schema). So to create a table in another schema you need to explicit the schema name, something like this:
CREATE TABLE [Staging].[AddisonLeeAirportLookUp] (ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(50))

By your comments you also did it so my guess is that you need to add or enable something in your database project. Also read MSDN forum and I've found a reference to add the schema to your SSDT project by performing the following steps:
"Right-click on your database project node in Solution Explorer and select Add->New Item. From the templates menu, select Security in the categories pane and select the Schema object."
1
 

Author Comment

by:shah36
ID: 41829710
Please cancel my request to close it down as Vitor has come with the perfect solution.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41829713
Question has been reopened.
0
 

Author Closing Comment

by:shah36
ID: 41829716
Thank you Vitor you are a star. This is the perfect solution.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41829718
Thank you for the opportunity to learn something more.
Cheers.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now