How to extract GRANTS and CONSTRAINTS on an Azure Synapse table into an executable sql script?

user_3234994 0
Viewed 40

We have a number of tables in Azure Synapse database with Round Robin distribution. Due to performance issues that come with the data shuffling, we want to convert some of these tables to HASH distribution.

We have created the steps to do this which involves creating a new table with HASH distribution and then doing a CTAS into this new table and then dropping the old table and renaming the new table to old table. The table name needs to be the same as there many Reporting views that use these tables.

But the challenge here is the CTAS only copies the data and not GRANTS and CONSTRAINTS during this process.

So, we want to extract the GRANTS and CONSTRAINTS of any given table into a .sql file so that once we create the new table. we can simply run this sql script and have the GRANTS and CONSTRAINTS in place just as before.

I can find out what GRANTS are given on a certain table through the below command:

EXEC sp_table_privileges @table_name = '<table_name>';

But there is no way to extract this information in the form of an executable sql script.

Is there a way to achieve this using SSMS? This would really help the database modificatons we're planning. Any ideas on how to accomplish this?

azure-synapse azure sql-server
1 Answers

You can generate all permissions using the following script

SELECT CONCAT(
    CASE WHEN p.state = 'W' THEN 'GRANT' ELSE p.state_desc END,
    ' ',
    p.permission_name,
    ' ON '
    QUOTENAME(s.name),
    '.',
    QUOTENAME(t.name),
    '(' + QUOTENAME(c.name) + ')',    -- The + feeds through nulls
    ' TO ',
    QUOTENAME(dp.name),
    CASE WHEN p.state = 'W' THEN ' WITH GRANT OPTION' END,
    ';'
  )
FROM sys.database_permissions p
JOIN sys.database_principals dp ON dp.principal_id = p.grantor_principal_id
JOIN sys.tables t ON t.object_id = p.major_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
LEFT JOIN sys.columns c ON c.object_id = t.object_id
    AND c.column_id = p.minor_id
    AND p.minor_id <> 0
WHERE t.name = 'YourTable';

Constraints can be done in a similar way, using various system views, but it's unclear exactly which constraints you are asking about.

user_148689970
Related