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.