Friday, February 23, 2024

Data Read Access to All the users in the specific database after refresh

 After you refresh database from Production to UAT environments make sure to run below scripts to drop production users first then grant data read access to UAT users.

use [Database_Name]
go
select  'DROP USER' , '['+ name+']'  
from dbo.sysusers
where name LIKE '%PRD-%' 
GO

use [Database_Name]
go
select 'EXEC sys.sp_addrolemember '   ,   ''' db_datareader '''   ,   '['+ name+']'
from master.dbo.syslogins
GO

Friday, February 16, 2024

Generate Foreign Key constraints for all table in Database

SELECT 'ALTER TABLE [dbo].[' +''+  object_name(fk.parent_object_id)+']' AS  ParentTableName, 

   'WITH NOCHECK ADD  CONSTRAINT'+' ['+  fk.[name] +']' ,    

   'FOREIGN KEY([' +''+  COL_NAME(fc.parent_object_id,fc.parent_column_id) +'])' AS  ParentColName ,

   'REFERENCES [dbo].[' +''+  object_name(fk.referenced_object_id) +']' AS  RefTableName,

   '([' +''+    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) +'])' AS  RerfColName ,

   ' NOT FOR REPLICATION'

FROM sys.foreign_keys fk

 INNER JOIN   sys.foreign_key_columns AS fc        

        ON fk.OBJECT_ID = fc.constraint_object_id

 INNER JOIN   sys.tables t 

      ON t.OBJECT_ID = fc.referenced_object_id

--WHERE fc.parent_object_id = object_id('Table_name')

Wednesday, February 07, 2024

Check DATABASEPROPERTYEX ()

If you are managing more than 'n' of SQL Server database estate in your organisation then you may required to check some times  a specified database current setting in SQL Server to understand.

This below function returns the current setting of the specified database option or property.

DATABASEPROPERTYEX ( database , property )

-- You should defined database name for each function to retrieve the property details

SELECT 
DATABASEPROPERTYEX('AdventureWorks2022', 'Collation') AS Collation, 
DATABASEPROPERTYEX( 'AdventureWorks2022' , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX('AdventureWorks2022', 'Recovery') AS Recovery_State


--Alternatively you can use db_name function for the current database to  retrieve the property details

USE [db_name]
GO
SELECT   
DATABASEPROPERTYEX( db_name() , 'Collation') AS Collation,
DATABASEPROPERTYEX( db_name() , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX( db_name() , 'Recovery') AS Recovery_State


-- Check with Sys.databases to see all the database related property values

SELECT * FROM sys.databases;

Saturday, February 03, 2024

What is new in SQL Server 2022

The new SQL Server 2022 version is a game changer for Data Analytics and Security which also improved  with Availability Group and Performance area. Please check it out  below link to Microsoft Learn Page

https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Feature highlights in SQL Server 2022

The following sections identify features that are improved our introduced in SQL Server 2022 (16.x).

https://learn.microsoft.com/en-us/training/modules/introduction-to-sql-server-2022/2-deploy-and-feature-difference

Features removed or deprecated in SQL Server 2022

The following features have been removed from SQL Server 2022 that were available in previous releases:

  • R, Python, and Java runtimes - R, Python, and Java runtimes are no longer included as part of the setup for SQL Server 2022. The Machine Learning Services feature is still supported, but you'll need to add your own packages that include runtimes you need.

  • Polybase Hadoop Connectivity with Java - The Polybase feature with Hadoop connectivity is removed from SQL Server 2022. You can still use Polybase services with ODBC drivers or new REST API based connectors for Azure Blob storage, Azure Data Lake Storage, or S3 compatible object storage.

  • Polybase scale out groups - The Polybase scale out group feature has been removed from SQL Server 2022. Queries using external tables or OPENROWSET for data virtualization can take advantage of scale-up processing built into SQL Server.

  • Machine Learning Server - Machine Learning Server was retired in July of 2022. Therefore, the Machine Learning Server feature has been removed from the SQL Server setup.

  • Distributed Replay - Distributed Reply is no longer available to configure with the setup for SQL Server 2022 on Windows.

  • Stretch Database - Stretch Database is deprecated in SQL Server 2022. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Friday, January 19, 2024

Microsoft Fabric

Microsoft Fabric is a new end-to-end Data and Analytics Platform. This new platform connect with Microsoft’s OneLake data lake,  This is going to game changer for Data Analytics Platform (DAP).

Image Credit : Microsoft

The Data Analytics workloads like below are inter connected to perform all in one environment
  • Data integration  
  • Data Engineering  
  • Data warehousing  
  • Data science 
  • Real-time analytics 
  • Business intelligence  
Check it out https://www.microsoft.com/en-us/microsoft-fabric


Thursday, January 18, 2024

Microsoft SQL Server 2022

Happy new year  2024 to everyone. Thank you so much for visiting this blog to learn new things in your life. I wish you all the best.

I always happy to share the new Microsoft SQL Server related features here and I have installed SQL Server 2022 Developer Edition in my laptop to explore new features in Server level and T-SQL too.



Monday, October 02, 2023

Row Level Security (RLS) Data Access in Azure SQL Database

We have always seen database level access to the end user but this following walk through will help you to understand How to setup Row Level Security(RLS) access for Azure Active Directory Group (AAD Group) for Table in Azure Database.

This entire SQL scripts developed and tested by myself with AAD Group. There are fours section and follow the each step to achieve your goal.

--********************************************************** 

  --PART 1 Database level User Access RLS AAD

  -- End User Added on below 3 AAD Groups  

--**********************************************************

--Group Name: AZ-AS-GRP1 

--Group Name: AZ-AS-GRP2

--Group Name: AZ-AS-GRP3


  -- STEP 1 Create User

CREATE USER [AZ-AS-GRP1] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA=[dbo]

GO

CREATE USER [AZ-AS-GRP2] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA=[dbo]

GO

CREATE USER [AZ-AS-GRP3] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA=[dbo]

GO

-- STEP 2 Database Role

CREATE ROLE db_RLS_DataReader   AUTHORIZATION [dbo]

GO

--STEP 3 Grant select permission to Database Role

 GRANT SELECT ON OBJECT::RPT.DIM_PRODUCT_SALES TO [db_RLS_DataReader] 


  -- STEP 4 Add an existing user to the role

ALTER ROLE [db_RLS_DataReader] add member  [AZ-AS-GRP1] 

ALTER ROLE [db_RLS_DataReader] add member  [AZ-AS-GRP2]

ALTER ROLE [db_RLS_DataReader] add member  [AZ-AS-GRP3] 

--================================================

 -- Validate the Role

SELECT  

    members.name as 'members_name', 

    roles.name as 'roles_name',

    roles.type_desc as 'roles_desc' ,

    members.type_desc as 'members_desc'

FROM sys.database_role_members rolemem INNER JOIN sys.database_principals roles

ON rolemem.role_principal_id = roles.principal_id

INNER JOIN sys.database_principals members ON rolemem.member_principal_id = members.principal_id

WHERE roles.name ='db_RLS_DataReader'


 --********************************************************** 

 --PART 2 Row Level User Access Table

 --********************************************************** 

 --  STEP 5   RLS User Access Table

CREATE TABLE [RPT].[DIM_DATA_USER_ACCESS](

[ID]  [int]  IDENTITY(1,1) NOT NULL ,

[Assign_Nm]  [varchar](20) NULL,

[AAD_Group_Name]     [nvarchar] (120) NULL,

[User_Email_Id]  [varchar] (60) NULL,

[Is_Active] [bit]  default(1) NULL,

[Deletion_DT] [datetime] NULL, -- 1 Active , 0 InActive

[Created_By]    [varchar](50)  DEFAULT  suser_sname() NULL,

[Created_Date]  [datetime] DEFAULT  getdate()  NULL,

[Modified_By]   [varchar](50)  DEFAULT  suser_sname() NULL,

[Modified_Date] [datetime] DEFAULT  getdate()  NULL,

 CONSTRAINT [PK_ID2] PRIMARY KEY CLUSTERED 

 (

 [ID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO 

 -- STEP 6: Create the specified users

CREATE USER FULL_DATA WITHOUT LOGIN;

GO

CREATE USER SALES_DATA1 WITHOUT LOGIN;

GO

CREATE USER IT_DATA2 WITHOUT LOGIN;

GO

CREATE USER PRO_DATA3 WITHOUT LOGIN;

GO


 -- INSERT  DATA

SELECT * FROM [RPT].[DIM_DATA_USER_ACCESS]  where User_Email_Id =user_name()

 INSERT INTO [RPT].[DIM_DATA_USER_ACCESS]  (  [ID], [Assign_Nm], [AAD_Group_Name]  , [User_Email_Id])

 VALUES (1, 'SALES_DATA1' , 'AAD1',  'MyUser1@abc.com'  )

 INSERT INTO [RPT].[DIM_DATA_USER_ACCESS]  (  [ID], [Assign_Nm], [AAD_Group_Name]  , [User_Email_Id])

 VALUES (2, 'IT_DATA2',  'AAD2',  'MyUser2@abc.com'     )

 INSERT INTO [RPT].[DIM_DATA_USER_ACCESS]  (  [ID], [Assign_Nm], [AAD_Group_Name]  , [User_Email_Id])

 VALUES (3, 'PRO_DATA3', 'AAD3',  'MyUser3@abc.com' ) 


-- STEP 7 Grant Access to Specified User

GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS]  TO FULL_DATA;

GRANT UPDATE ON [RPT].[DIM_DATA_USER_ACCESS]  TO FULL_DATA;

GRANT DELETE ON [RPT].[DIM_DATA_USER_ACCESS]  TO FULL_DATA;

GRANT INSERT ON [RPT].[DIM_DATA_USER_ACCESS]  TO FULL_DATA;


GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS]  TO [AZ-AS-GRP1]

GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS]  TO [AZ-AS-GRP2]

GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS]  TO [AZ-AS-GRP3]


 --********************************************************** 

 --PART 3 Security and Functions

 --********************************************************** 

 -- STEP 8: Create the inline table-valued function

 -- DROP FUNCTION [dbo].[fn_MY_Security]

 --GO 


CREATE FUNCTION  dbo.fn_MY_Security

(@MAPPING_NAME NVARCHAR (255) ) --, @USER_NAME NVARCHAR (255)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN 

SELECT 1 AS fn_MY_Security_Result

WHERE @MAPPING_NAME IN 

( SELECT DISTINCT DH.[Assign_Nm]

   FROM [RPT].[DIM_MY_DEPT] AS DH

   INNER JOIN [RPT].[DIM_DATA_USER_ACCESS] AS UA

   ON DH.[Assign_Nm] = UA.[Assign_Nm] 

    WHERE (LOWER( [User_Email_Id])  = LOWER( USER_NAME() ) -- Specific User

AND [Is_Active]=1) OR LOWER( USER_NAME() ) = 'FULL_DATA' -- Admin User

)            

GO


 -- STEP 9: Apply the Security Policy

 -- DROP SECURITY POLICY [dbo].[MY_UserFilter]

 -- GO

CREATE SECURITY POLICY [dbo].[MY_UserFilter]

ADD FILTER PREDICATE   [dbo].fn_MY_Security([Assign_Nm])   

ON [RPT].[DIM_DATA_USER_ACCESS]  

WITH (STATE = ON);

GO

  --********************************************************** 

 --PART 4   Data Validation

  --********************************************************** 

  -- -- STEP 10 Data Validation

-- All data for Admin RLS


EXECUTE AS USER = 'FULL_DATA';

SELECT * FROM  [RPT].[DIM_DATA_USER_ACCESS]

  WHERE AAD_Group_Name LIKE 'AZ%'    

REVERT;

GO


EXECUTE AS USER = 'FULL_DATA';

SELECT * FROM  [RPT].[DIM_DATA_USER_ACCESS]

  WHERE AAD_Group_Name LIKE 'AZ%'     

  ORDER BY 2 

REVERT;

GO


--Specific users

EXECUTE AS USER = 'SALES_DATA1';

SELECT * FROM  [RPT].[DIM_DATA_USER_ACCESS]

REVERT;

GO


EXECUTE AS USER = 'IT_DATA2';

SELECT * FROM  [RPT].[DIM_DATA_USER_ACCESS]

REVERT;

GO


EXECUTE AS USER = 'PRO_DATA3';

SELECT * FROM  [RPT].[DIM_DATA_USER_ACCESS]

REVERT;

GO


 --This catalog view returns all the Security Policies in the database

 --Execute the following statement to get all the security policies in the database with important security policy attributes/columns


SELECT Name, object_id, type, type_desc,is_ms_shipped,is_enabled,is_schema_bound

FROM sys.security_policies

 

--This catalog view returns all the Security Predicates in the database

SELECT * 

FROM sys.security_predicates