[SQL] SQL #sql

sql_basics.md

SQL DB conn string secret example:

Data Source=<servername>.database.windows.net;Initial Catalog=<dbname>;User ID=<svcaccount>;Password=<PW>;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;MultipleActiveResultSets=true

Create new Service Account:

• Master:

Create login svc_deploy with password = '<PW>';

• DB:

create user svc_deploy for login svc_deploy;
exec sp_addrolemember 'db_owner', svc_deploy;

Add a AD group directly to a DB:

• DB:

CREATE USER [<AD GROUP NAME>] FROM EXTERNAL PROVIDER;
exec sp_addrolemember 'db_owner', [<AD GROUP NAME>] ;

Check roles:

SELECT r.name role_principal_name, m.name AS member_principal_name
    FROM sys.database_role_members rm 
    JOIN sys.database_principals r 
        ON rm.role_principal_id = r.principal_id
    JOIN sys.database_principals m 
        ON rm.member_principal_id = m.principal_id

Drop a role:

exec sp_droprolemember db_owner, svc_dwhr_sync

Add a permission to svc:

GRANT CREATE PROCEDURE TO svc_dwhr_prod_sync
GRANT CREATE TYPE TO svc_dwhr_prod_sync

Roles examples:

db_owner db_datawriter db_datareader

Create external data source

1. create login svc with read access on the db ('svc_dwhr_read_cusa')
2. create user for this svc login in both source db ('db_datareader') and consumer db ('db_owner)
3. Then execute in the consumer db:

--STEP 1 - Create chemas 

CREATE SCHEMA cusa;

--STEP 2 - Change Schema ownership to dbo
ALTER AUTHORIZATION ON SCHEMA::cusa TO dbo

--STEP 3 Create external table credentials - To be done by Infra. Check if not already exists
BEGIN
CREATE DATABASE SCOPED CREDENTIAL svc_dwhr_scoped_cusa
-- 'svc_dwhr_read_cusa' currently present in both dwhr (db_owner) and cusa(db_datareader) DBs
WITH IDENTITY = 'svc_dwhr_read_cusa',
-- secret for 'svc_dwhr_read_cusa' (stored in Vault)
SECRET = '';
END


CREATE EXTERNAL DATA SOURCE cusa_db WITH
(
    TYPE = RDBMS,
    LOCATION = '<SERVER_NAME>.database.windows.net',
    DATABASE_NAME = 't-sn-cusa-1-sdb',
    CREDENTIAL = svc_dwhr_scoped_cusa
)
GO

DB sync notes:

set membersync to 0 when you want to start everything from scratch recommended via azure support this resulted in very slow sync -> not really usefull UPDATE dss.syncgroupmember SET memberstate = 0 WHERE name = 'Client_QA'

https://portal.azure.com/#blade/Microsoft_Azure_Monitoring_Logs/LogsBlade/initiator/InsightsLiveLogs.ReactView/scope/%7B%22resources%22%3A%5B%7B%22resourceId%22%3A%22%2Fsubscriptions%2Fc640b8b6-134c-4f43-bf52-1a3c46cb4465%2FresourceGroups%2Ft-sn-btcsa-rsg%2Fproviders%2FMicrosoft.OperationalInsights%2Fworkspaces%2Ft-sn-btcsa-law%22%7D%5D%7D/query/