We have created Test AD Group and they should have readonly access to the database (schemas and tables, views) in the Azure SQL dedicated pool. Our DBA team did it but the users in the Test AD Group cannot login until they select the database as the default database in the connection dialog in SSMS. Is there a way to allow them to login while ONLY have readonly access to the database (including future schemas and tables that will be created)?

We're moving all our on premise tables into the azure dedicated sql pool. We're using synapse workspace pipelines to import the data into the dedicated pool. Is there a way to checksum a table to compare it against on premise table to make sure everything was imported correctly? I know in SQL Server there is a checksum function for a table but apparently it is not in

I will give a few examples of the tables we have: Table1 - Most queries are State Code and Year, has about 1000 rows, will grow by 1000 rows a year, used Round Robin Table2 - Most queries are by State Code, Year, Column 1 (String), has about 1000 rows, will grow by 1000 rows a year, used Round Robin Table3 - Most queries are by State Code and Year, has about 100,000 rows, will grow by about 25000 rows a year, used Hash Table4 - Most queries are by Year, has about 100,000 rows, will grow by about 25000 rows a year, plan to use Hash Lookup Table 5 - Most queries are by State Code and Look up Id, has about 10 rows, used Replication Lookup Table 6 - Most queries are by State Code and Look pup Id, has about 500 rows, used Replication Did we use the correct types of distribution? Can someone give a more concrete/better example/clarification of when/why you should use each type of distribution? Microsoft's documentation/guidance wasn't very helpful.

I have a materialized view in Azure synapse Analytics dedicated sql pool which has a calculate column X / denominator. When **I select the view select * from myview** it does not give me divide by zero error. But when I select Select * from myview where denominator = 0 I get divide by zero error When I use **select * from myview order by denominator** I am not getting divide by zero error. How can this be explained. I know the Arithabort setting but not sure how to check the value set to this in Synapse dedicated SQL Pool. Any help would be appreciated.

I'm keen to get a list of the feature limitations or suprises when using Synapse Analytics (a.k.a. Dedicated SQL Pool, used to be known as SQL DW / SQL Data Warehouse) compared to using normal SQL Server. Basically the things that you would expect to be there, but aren't.

I know that it's a repeated question but I've tried all solution and workaround suggested from every possible question present on stackoverflow.com I have inherited a dedicated SQL Pool with a SQL Server version 2012.0.2000.8 on which there are several tables.
We're looking to understand how much retention should we set and to do that we're analyzing the size of every table. I've tried - https://dba.stackexchange.com/questions/112167/show-data-and-disk-use-breakdown-by-table/112194#112194 - https://dba.stackexchange.com/questions/84333/space-usage-on-sys-allocation-units-and-sp-spaceused?noredirect=1&lq=1 - Generating report by clicking on the right of database, but I don't have that option in SSMS Tried to write this query SELECT OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id) AS TableName, SUM(p.rows) AS RowCounts, CONVERT(DECIMAL,SUM(p.reserved_page_count)) * 8 / 1024 AS TotalSpaceMB, CONVERT(DECIMAL,SUM(p.used_page_count)) * 8 / 1024 AS UsedSpaceMB , CONVERT(DECIMAL,SUM(p.reserved_page_count - p.used_page_count)) * 8 / 1024 AS UnusedSpaceMB FROM sys.partitions p WHERE p.index_id 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY UsedSpaceGB DESC, t.Name Everytime there is a use of Sys Views (sys.schemas / sys.partition / sys.allocation_units etc. etc.) it says
**Catalog view 'X' is not supported in this version.** I think that mostly there could be a problem with some permission since I cannot use system views and they should be present in my version of SQL Pool.
So, my question is,
**how I can fix using system views ?**

In an Azure Synapse dedicated SQL pool I have the following setup:

-- a custom DB role to manage privileges
CREATE ROLE [owner];

-- there is a schema owned by this role
CREATE SCHEMA [myschema] AUTHORIZATION [owner];

-- an Azure AD group to allow its members to log in
CREATE USER [radish] FROM EXTERNAL PROVIDER;

-- the AAD group is a member of the owner role
EXEC sp_addrolemember 'owner', 'radish';

-- privileges are assigned exclusively through custom DB roles
GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner];

If I log in, being a member of the radish AAD group, I cannot create a table in myschema:

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.

Once I get the Synapse Administrator role, this works. (When a member, SESSION_USER is dbo.) This is no surprise, as this role can do basically everything inside the SQL pools of the given workspace. However, when I remove myself from the Synapse Administrator role, the following happens:

DROP TABLE [myschema].[test];
-- completes successfully, but then:

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.

Probably my understanding about how the necessary privileges for CREATE TABLE and DROP TABLE relate to one another is totally wrong, but until now I thought it were the same privileges needed for both. Can someone please show me where my thinking is wrong? So far I see the following possibilities: - the owner role doesn't apply to users that are only indirectly member of it (i.e. through membership in the AAD group) - this seems to contradict https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control#step-8-add-users-to-security-groups - the table created when having enough privileges is owned by myself and not the schema owner role. The 'Caution' section contradicts: 'A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema's owner.' Also, sp_tables reports owner as being table_owner. Also, interestingly, I can do a GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner]; successfully, whereas creating the table fails.