How to implement row-level security in serverless SQL pools
Row-level security enables you to configure your views to return different subsets of rows to the users with difference permissions. This way, the readers will get limited results based on their access rights or roles. Row-level security can be easily implemented in serverless SQL pools using the instructions in this post.
Sql Query
Hi All, I have a requirement. want output data like below screen print CREATE TABLE [dbo].[Source_Table]( [Product] [varchar](10) NULL, [Trans_type] [varchar](10) NULL, [SalesCode] [varchar](10) NULL, [Profitamount] [int] NULL ) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type1', N'A', 7) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product2', N'Type1', N'B', 3) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type2', N'C', 4) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product2', N'Type2', N'A', 5) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type1', N'D', 9) GO Thanks in Advance...Create alerts for your Synapse Dedicated SQL Pool
In this article I will discuss how to configure alerts for you Azure Synapse dedicated SQL pool and provide recommended alerts to get you started. Enabling alerts allows you to detect workload issues sooner allowing you to take action earlier to minimize end-user impact.Deploying Synapse SQL Serverless objects across environments using SSDT
The long-awaited feature for all Synapse CICD fans is here! SqlPackage now supports serverless SQL pools in Extract and Publish operations. In this article, I will demonstrate how you can run this utility in a DevOps pipeline to replicate your SQL serverless objects across different environments.Read Only Permissions in Synapse SQL
The Object level / Schema level permissions can be provided in Azure Synapse Analytics using SQL statements. There are be several ways that this can be achieved with different SQL syntax. EXEC sp_addrolemember 'db_datareader' , 'UserName' The Syntax “sp_addrolemember” Adds a database user, database role, Windows login, or Windows group to a database role in the current database. The Syntax “db_datareader” Members of the db_datareader fixed database role can read all data from all user tables. GRANT Database Principal Permissions also another way that can be achieved this task. This can be extended to table / schema level permissions. GRANT SELECT ON DATABASE::[SQL pool Name] TO [UserName] -------------------------------------------------------------------------------------------------------------- For Testing purposes, we have created 10 tables in the Data warehouse as follows – -------------------------------------------------------------------------------------------------------------- Example #1 : Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “sp_addrolemember” Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL pool – EXEC sp_addrolemember 'db_datareader' , 'UserName' -------------------------------------------------------------------------------------------------------------- The role permission level can be check after providing the access to the specific user using “sp_addrolemember” as follows - SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.name ='db_datareader' -------------------------------------------------------------------------------------------------------------- The role permission level verifications as follows – -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- Example #2: Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions Connect to SQL pool using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL pool – GRANT SELECT ON DATABASE::[SQL pool Name] TO [UserName] -------------------------------------------------------------------------------------------------------------- The permission level can be check after providing the access to the specific user using “GRANT SELECT ON DATABASE” as follows - select princ.name, princ.type_desc, perm.permission_name, perm.state_desc, perm.class_desc, object_name(perm.major_id) from sys.database_principals princ left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id where princ.name = '[USERNAME IN DW]' -------------------------------------------------------------------------------------------------------------- The GRANT DATABASE PRINCIPAL permission level verifications as follows – -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- NOTE: To remove the permissions or user roles, below syntax can be used To drop a user from user role – “sp_droprolemember” To drop a database principal permission – “REVOKE ON DATABASE PERMISSION” -------------------------------------------------------------------------------------------------------------- NOTE : When “Select” permissions are provided, the database objects will be visible to the user who has permissions and to hide the objects and provide access to specific objects, this needs to be extended to object level / schema level permissions Example #3: Note: There are multiple tables created with different schemas in the test environment - Providing “Select” permission to a SQL Schema on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL Schema & verify – GRANT SELECT ON SCHEMA::[SCHEMA NAME] TO [UserName] -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- Verifications using SQL server Object Explorer once the user is connected. The user will be able to see the objects under the schema (that permission provided) -------------------------------------------------------------------------------------------------------------- Example #4: Note: There are multiple tables created with different schemas in the test environment - Providing “Select” permission to a SQL Object on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL Object & verify – GRANT SELECT ON OBJECT::SCHEMANAME.TABLENAME TO [UserName] -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- Verifications using SQL server Object Explorer once the user is connected. The user will be able to see the objects under the schema (that permission provided)
