Forum Discussion
New Fabric SQL Datasbase does not let me CREATE TABLE from SSMS
With Fabric SQL Databases being so new, there WILL be learning curves (and bumps in the road) as we, the community struggle to adopt them and learn of the nuance differences. One key difference is the Connection Strings.
I have been using the Connection String from a Fabric Warehouse or Lakehouse in SSMS for quite some time (I usually set up Registered Servers for them.) And that Connection String exposes all of the database type objects under one server. You can even run cross-database queries if you want to move data from a Lakehouse to a Warehouse. And the new Fabric SQL Database is also there. But it is NOT there as a typical SQL Database because the SQL Endpoint is an ANALYTIC Endpoint.
If you open the SQL Database in the browser, then click "Open in" >> "SQL Server Management Studio":
You get a dialog that shows the SQL Endpoint and a Database name (which is your database plus a GUID after it).
Compare this connection string to the one from the Lakehouse endpoint. They will be different:
(same 'server' GUID, but different suffixes)
Behind the DATABASE Endpoint you have full SQL Database capabilities: PRIMARY KEYS, INDEXES, etc.
Behind the ANALYTIC/DATA WAREHOUSE Endpoint you have ANALYTIC capabilities.
One allows CREATE TABLE and one does not. But same data.
Microsoft is still working on things like removing the GUID from the SQL Endpoint Database name, and other minor bugs.
This was an invaluable revelation for me pointed out by someone in the forums.
Hi ToddChitt ,
I am currently unable to create a SQL database in Fabric, but found the same thing when connecting to the data warehouse in SSMS.
This error message indicates that an external policy denial was encountered while executing the CREATE TABLE statement. This usually means that the current privileges are insufficient to perform the operation.
I found a way:
First, use the following statement in SSMS to query the user currently in use.
SELECT SYSTEM_USER.
Create a SQL query in Fabric warehouse and assign the db_owner role to the current user using the following statement.
USE [warehouseTest1]; -- Replace with your database name
-- Grant db_owner role to the user
ALTER ROLE db_owner ADD MEMBER [[email protected]]; -- Replace with your username
Now go back to SSMS and you can create the table. The result is shown below:
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!