Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

Schema Not Showing in SSDT Database Project

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
shah36
Asked:
shah36
  • 11
  • 8
  • 4
  • +1
1 Solution
 
Russ SuterCommented:
Does the login you're using have permissions for the Staging schema?
0
 
shah36Author Commented:
Yes i can use this Schema in SSMS. I have logged in with same credentials in SSDT as of SSMS
0
 
Pawan KumarDatabase ExpertCommented:
change authorization and then try.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
shah36Author Commented:
Hi Pawan,
Where do i need to change authorization?

regards
0
 
Pawan KumarDatabase ExpertCommented:
Sample..

ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;    
GO
0
 
shah36Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Close the SSMS and try again.
0
 
shah36Author Commented:
same error again. i tried running it on Master database too
0
 
Pawan KumarDatabase ExpertCommented:
Try to use sp_changedbowner stored procedure.
0
 
shah36Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
shah36Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
shah36Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
shah36Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh, so it may not even be a bug but a limitation of the SSDT.
0
 
shah36Author Commented:
Yes you are right. So should i just close the question
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, maybe is better to close it unless you have some kind of hope that somebody else can help you with this.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
shah36Author Commented:
Please cancel my request to close it down as Vitor has come with the perfect solution.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Question has been reopened.
0
 
shah36Author Commented:
Thank you Vitor you are a star. This is the perfect solution.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thank you for the opportunity to learn something more.
Cheers.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now