paschott

SELECT
state_desc + ' ' + permission_name + ' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']' COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
  ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
  ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
  ON sdp.grantee_principal_id = sdpr.principal_id

paschott

CREATE EVENT SESSION [Long-Running Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
    sqlserver.database_id, sqlserver.database_name, sqlserver.nt_username,
    sqlserver.query_hash, sqlserver.server_principal_name,
    sqlserver.session_id, sqlserver.sql_text )
    WHERE ( ( ( package0.greater_than_uint64(sqlserver.database_id, ( 4 )) )
              AND ( package0.equal_boolean(sqlserver.is_system, ( 0 )) ) )
            AND ( duration >= ( 500000 ) )
          ) ),

paschott

--1: Create Temp table
CREATE TABLE #MyFactTable(
    ID INT NULL
    , CustomerName VARCHAR(100) NULL
    , SourceID INT NULL
    , OutputAction VARCHAR(100) NULL
);
 
--2: INSERT into the temp table instead of your normal target table
--   Merge query will be the same otherwise

paschott

-------------------------------------------------------------
-- Create Audit Database
-------------------------------------------------------------
USE master
GO
CREATE DATABASE Audit
GO

USE [Audit]
GO

paschott

--Need details on creating a batch file and running it. Include a "PAUSE" and newline at the end.

--Note - need to create the appropriate folders. Default is "C:\bcp\databasename".
--Source
SELECT 'bcp ' + DB_NAME() + '.dbo.' + Table_name + ' OUT "C:\bcp\' + DB_NAME() + '\' + Table_name + '.bcp" -S' + @@servername + ' -N -T -E -b 100000'
FROM Information_Schema.Tables
WHERE Table_Schema = 'dbo'
ORDER BY TABLE_NAME
SELECT 'PAUSE
'

paschott

--Create test table to represent data we want to import.
CREATE TABLE #ProductsToImport (
    OriginalSystemID INT,
    ProductName NVARCHAR(50),
    ProductNumber NVARCHAR(25),
    SafetyStockLevel SMALLINT,
    ReorderPoint SMALLINT,
    StandardCost MONEY,
    ListPrice MONEY,
    DaysToManufacture INT,

paschott

--Note that this uses a DB Mail Profile named "Default".
CREATE PROC dbo.pr_SendSSISErrors
	@FolderName sysname = NULL,
	@ProjectName sysname = NULL,
	@PackageName sysname = NULL,
	@ToAddress NVARCHAR(500)
AS
BEGIN --Proc
SET NOCOUNT ON

paschott

# lets just bucket all the comments from all users and then print it out
# for each file, read the file sequentially and add it to a hash table count

$database = "SQLSlack"
$server = "localhost"
$parent_folder = "C:\SlackExport"
Import-Module SqlServer


function Invoke-Sql {

paschott

--Create table to store posts
CREATE TABLE [dbo].[Posts](
	[Username] [nvarchar](255) NULL,
	[Type] [nvarchar](255) NULL,
	[Subtype] [nvarchar](255) NULL,
	[TS] [nvarchar](255) NULL,
	[Channel] [nvarchar](255) NOT NULL,
	[MessageText] [nvarchar](max) NULL,
	[MessageDate] [datetime2](7) NULL,
	[PostID] [bigint] IDENTITY(1,1) NOT NULL,

paschott

--replace the URL in the function with the appropriate URL for your file
SELECT TOP 1000 *
FROM sys.fn_get_audit_file('https://subscription.blob.core.windows.net/paths/auditfile.xel', default, default)
WHERE (event_time <= '2019-02-20T21:56:36.631Z')
/* additional WHERE clause conditions/filters can be added here */
and action_id IN ('DBAF') --look specifically for auth failures
ORDER BY event_time DESC