Link to home
Start Free TrialLog in
Avatar of Ali Shah
Ali ShahFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Russ Suter
Russ Suter

Does the login you're using have permissions for the Staging schema?
Avatar of Ali Shah

ASKER

Yes i can use this Schema in SSMS. I have logged in with same credentials in SSDT as of SSMS
change authorization and then try.
Hi Pawan,
Where do i need to change authorization?

regards
Sample..

ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;    
GO
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.
Close the SSMS and try again.
same error again. i tried running it on Master database too
Try to use sp_changedbowner stored procedure.
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

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?
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
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?
Here is the picture User generated image 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.
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?
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.
Oh, so it may not even be a bug but a limitation of the SSDT.
Yes you are right. So should i just close the question
Yes, maybe is better to close it unless you have some kind of hope that somebody else can help you with this.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please cancel my request to close it down as Vitor has come with the perfect solution.
Question has been reopened.
Thank you Vitor you are a star. This is the perfect solution.
Thank you for the opportunity to learn something more.
Cheers.