External Tables in SQL

CREATE DATABASE SCOPED CREDENTIAL sql_user_bob
WITH
     IDENTITY = 'sql_user_bob',
     SECRET = 'dtgf#45322@d343' ;
go

CREATE EXTERNAL DATA SOURCE [NameExternalDB]
WITH (TYPE = RDBMS, LOCATION = N'ExternalSerer.database.windows.net',
CREDENTIAL = [datahub_reader], DATABASE_NAME = N'ExternalDB')
GO

CREATE EXTERNAL TABLE [dbo].[dimUsers]
(
[UserId] [int] NULL,
....
)
WITH (DATA_SOURCE = [NameExternalDB] ,SCHEMA_NAME = N'common',OBJECT_NAME = N'dimUsers')
GO

History tracking for SQL

Add history tracking to the existing table

alter table tbl_event2
add SysStartTime datetime2 generated always as row start not null default getutcdate(),
    SysEndTime   datetime2 generated always as row end   not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
    period for system_time (SysStartTime, SysEndTime);

alter table tbl_event2
  set (system_versioning = on (HISTORY_TABLE = dbo.tbl_event2_history));

PowerShell Power

Business Team was setting up a Teams channel with 250+ Partners. They reached out me saying there is not bulk upload support.  After few search I found this Power Shell support.

It was quick and the job get done.  Thanks to the Teams for enabling PowerShell.

Register-PSRepository -Name PSGalleryInt -SourceLocation https://www.poshtestgallery.com/ -InstallationPolicy Trusted
Install-Module -Name MicrosoftTeams -Repository PSGalleryInt -Force
Get-Module -Name MicrosoftTeams

Connect-MicrosoftTeams

Add-TeamUser -GroupId a51695fe-a4bf-49b0-887d-fbd60 -User "...@...com"

Add-TeamChannelUser -GroupId a51695fe-a4bf-4887d-7f79b81fbd60 -DisplayName " Events - Program Info" -User "..@...ch"

India Election Visual

Place holder blog

It is interesting to see how the election visuals are communicated. will write more about this when I get time.

TSql Script out data

In SQL Server Management Studio (SSMS) you can generate script out the DB Object, you can also generate script for data (Insert statements for each row).  Here are the steps.

Managing SQL Role

Recently, we had a scenario, where we need a simple SQL Role which gets read access to bunch of views.  While it was easy to create the view and grant permissions.  There is no simple sample on the web to get the list of permissions the role has.  

After few search and scrubbing the code available on the we, we come with below snippet useful.

--Create a role
CREATE ROLE DB_EXTERNAL_READER

--Grant permission to the role on objects
GRANT SELECT ON vwNpsResponses TO DB_EXTERNAL_READER
GRANT SELECT ON vwNPSResponsesV2 TO DB_EXTERNAL_READER

DENY SELECT ON vwNPSResponsesV2 TO DB_EXTERNAL_READER

--Check which objects the role have access to.
SELECT permission_name, state_desc, object_name(major_id), name
 FROM sys.database_permissions p
     INNER JOIN sys.database_principals dp
           ON p.grantee_principal_id = dp.principal_id
where dp.name = 'DB_EXTERNAL_READER'

--Make sure whenever you drop/create objects you need to re-grant the permissions.

--Use sp_addrolemember to add the user to the role

sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'

To check the access

WITH perms_cte as
(
    SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
           dp.principal_id,
           dp.type_desc AS principal_type_desc,
           p.class_desc,
           OBJECT_NAME(p.major_id) AS object_name,
           p.permission_name,
           p.state_desc AS permission_state_desc 
      FROM sys.database_permissions p
     INNER JOIN sys.database_principals dp
           ON p.grantee_principal_id = dp.principal_id
),perms AS(
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
  FROM    perms_cte p
 WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
  FROM perms_cte p
 RIGHT OUTER JOIN (
    SELECT role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
      FROM sys.database_role_members rm
     INNER JOIN sys.database_principals dp
           ON rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id)
SELECT *
FROM Perms
WHERE principal_name = 'User name'

Missing Date

I need to check in a table, is the all day data available, here is the handy script to check the same..

DECLARE @startdate DATETIME
       , @enddate DATETIME;


SET @startdate = '07/01/2018';
SET @enddate = '08/04/2018';

WITH CalendarDates
AS ( SELECT dt = @startdate
     UNION ALL
     SELECT DATEADD(DAY, 1, dt)
     FROM calendardates
     WHERE DATEADD(DAY, 1, dt) <= @enddate
), Summary AS(
SELECT DISTINCT dt, ReportUTCDate
FROM tblData
FULL OUTER JOIN CalendarDates ON dt = ReportUTCDate
WHERE ReportUTCDate IS NULL
)
SELECT * FROM Summary

Adding here for quick reference and to share with colleagues.