Happy Fourth of July (2019-07-04)

 SQLServerCentral Newsletter for July 4, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SSIS as an Execution Engine for Multiple Sql scripts]( - [Introduction to DevOps: The Evolving World of Application Delivery]( - [How to provision realistic and compliant data to dev – free whitepaper]( - [From the SQL Server Central Blogs - Job History – Back to Basics]( - [From the SQL Server Central Blogs - SQL Server Containers Are Boring]( Question of the Day - [July 4 2019]( Featured Script - [Audit Users logging in using "sa" or service accounts]( The Voice of the DBA  Happy Independence Day 2019 It's the Fourth of July in the US, and a holiday for me. If you're on break, I hope you don't read this today. If you are reading this, maybe it will be a slower day with most of the US off from work. A quiet holiday for me. With lots of trips planned across the summer months for work, and a vacation already in the books for 2019, I'll be using some time to catch up on ranch projects and likely cooking on the grill with family for a quiet day. Back tomorrow for a day, and then more projects on the weekend. Have a great day, wherever you are.  Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](  [Redgate SQL Provision](  Featured Contents [SSIS as an Execution Engine for Multiple Sql scripts]( Daniel Klionsky from SQLServerCentral.com Learn how you can execute multiple scripts in a restrictive database environment that doesn't allow any third-party tools. [Introduction to DevOps: The Evolving World of Application Delivery]( Additional Articles from SimpleTalk Today, more organisations are adopting DevOps over older, traditional practices. In this article, Robert Sheldon explains DevOps and compares it to Waterfall and Agile methodologies. [How to provision realistic and compliant data to dev – free whitepaper]( Site Owners from Redgate Compare common approaches to provisioning test data to database development teams, and assess how they stack up in terms of delivering realistic test data, managing bottlenecks, and meeting data privacy regulations. From the SQL Server Central Blogs - [Job History – Back to Basics]( SQLRNNR from SQL RNNR An important part of any DBAs job is to ensure database related jobs are running prim and proper. Sometimes that just doesn't happen. When jobs are being overlooked, it... From the SQL Server Central Blogs - [SQL Server Containers Are Boring]( Grant Fritchey from The Scary DBA Not really, but sort of. The beauty of containers, at least in a dev/test environment, is the ability to spin them up while you need them and then throw...   Question of the Day Today's question (by Steve Jones - SSC Editor):  July 4 2019 It's Independence day in the US. Everywhere else, it's just a Thursday. Except for some newsletter subscribers in New Zealand. It might be Friday there. If I want to get the beginning of July 4 in the US, which is midnight in the Eastern Time Zone, and I have the code below to give me the time in Christchurch, NZ. What is returned DECLARE @t DATETIMEOFFSET = 'July 4, 2019 00:00:00 -4:0'; SELECT @t AT TIME ZONE 'New Zealand Standard Time' ? DECLARE @t DATETIMEOFFSET = 'July 4, 2019 00:00:00 +5:0'; Think you know the answer? [Click here]( and find out if you are right.  [Redgate SQL Provision](    Yesterday's Question of the Day (by Steve Jones - SSC Editor) The Client Key Which key is stored on the client, or accessed by the client, and used for the encryption/decryption operations in Always Encrypted? Answer: The Column Master Key Explanation: In Always Encrypted, the Column Master Key is managed on the client. The Column Encryption Key is stored on the server in an encrypted format. Ref: Always Encrypted - [Discuss this question and answer on the forums](  Featured Script [Audit Users logging in using "sa" or service accounts]( MartinT from SQLServerCentral Security is probably one of a production DBA's most important tasks and if developers use application or service accounts to run queries in your production environment you need to identify then and prohibit it. I notice some unwanted activity after staring a new job and decided to create a script that notifies me via email when […] IF OBJECT_ID ('AuditSqlLogins') IS NOT NULL DROP TABLE AuditSqlLogins GO CREATE TABLE AuditSqlLogins ( [Counter] [int] IDENTITY(1,1) NOT NULL, [Login_name] [varchar](20) NULL, [host_name] [varchar](20) NULL, [login_time] [datetime] NULL ) ON [PRIMARY] INSERT INTO [dbo].[AuditSqlLogins] (login_time, login_name, host_name ) SELECT login_time, login_name, host_name FROM sys.dm_exec_sessions WHERE host_name not like '%APP%' -- excludeds Apllication server logins and host_name not like '%WWW%' -- excludeds Web server logins and database_id = 5 -- Specify the database you want to audit and login_name = 'Appadmin' -- specify the account you want audited or login_name = 'sa' ORDER BY host_name desc DELETE FROM AuditSqlLogins WHERE host_name is null DECLARE @counter integer SELECT @counter = Counter FROM AuditSqlLogins IF (@counter > 0) BEGIN DECLARE @xml NVARCHAR(MAX) SET @xml = CAST ( ( SELECT td = login_name , '', td = host_name, '', td = login_time,'' FROM AuditSqlLogins FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) DECLARE @body nvarchar(max); SET @body ='  Audit Apadmin and SA Login Event : (Server Name) ' SET @body = @body+@xml+' login_name host_name login_time ' EXEC msdb.dbo.sp_send_dbmail @recipients='youremail@yourwork.com', @subject = 'Audit itsadmin and SA Login Event', @body = @body, @body_format = 'HTML' , @profile_name='Your DB Mail profile' END DROP TABLE AuditSqlLogins [More »](  Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [USERSTORE_TOKENPERM growing very fast]( - Hi folks, I'm experiencing a very weird problem with userstore_tokenperm clerk recently. We have an environment with some databases in contained mode executing in version SQL Server 2017. Anyway i'm facing this problem since the SQL Server 2016. At a certain moment this cache starts to grow at a rate of 6GB by day. When […] SQL Server 2017 - Development [Problem writing a query to handle dynamic field usage]( - I have been presented with some source data and mapping tables where the source data is stored as : MyData Type Id Field1 Field2 Field3 1 1 MR John Smith 1 2 MRS Jane Doe 2 3 Bloggs Betty Miss what I would like to ideally be able to do is produce a view for […] SQL Server 2016 - Administration [where do i need to install SSRS]( - I am having an issue with my SSRS setup when I failover. Details of my setup are below: 2 node SQL 2016 Enterprise cluster. Read only intent connections are diverted to secondary node. Native Mode Install SSRS config manager is using the AG listener name for the SQL Server name. I have SSRS installed on […] [SQL logins with AD]( - I have a separate script for SQL Logins with the permissions db data reader etc and a separate script that reads AD to see if the user is terminated. I am looking to see if this can all be in one script.  Thanks  [compatibily Level 130 and traceflag 4199]( - Hello everyone I migrated my server SQL SERVER 2008R2 to 2016 I changed the compatibility level of my database to 130 USE [master] GO ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 130 GO Is it necessary after this activation? Activated traceflag 4199 is It automatically included when you change the COMPATIBILY LEVEL to 130 ? thanks […] [Solid State Drives and Fragmentation]( - Are there benefits to be had from defragging clustered indexes that reside on SSDs? Or is it true that since SSD's data is addressed via electronic switching on the SSD address bus there is no performance penalty due to fragmentation? SQL Server 2016 - Development and T-SQL [StoredProc insert and update multiuser issue]( - Happy to day to all, I am looking into an issue with a stored procedure where are where there are multiple users the code does the select but the update may fail: (as well i want to be educated on the solution so please push into a direction of learning as well) SET ANSI_NULLS ON […] Administration - SQL Server 2014 [Failover in FCI SQL with command SQL]( - Hello everyone on an FCI Failover cluster instance how can I failover to the second node without restarting the main node is there an SQL command or Powershell that allows me to do the failover thank you Development - SQL Server 2014 [Compare Dates]( - Hello All, create table #app1 ( custID INT, Seqs INT, eff_dt datetime, term_dt datetime ) insert into #app1 select 123, 100, '07/01/2017', '12/31/2017' union all select 123, 200, '01/01/2018', NULL union all select 456, 021, '08/01/2017', '11/30/2017' union all select 456, 021, '12/01/2018', '02/28/2019' union all select 456, 031, '03/01/2019', NULL union all select 789, […] [XACT_ABORT with ROLLBACK]( - I have come across some stored procedures in our company database that follow this format: SET XACT_ABORT ON BEGIN TRY BEGIN TRAN -- Do some stuff here -- Do some more stuff here COMMIT END TRY BEGIN CATCH ROLLBACK; THROW; END CATCH Am I correct in thinking that the CATCH code block will never run […] SQL Server 2012 - T-SQL [Get list Stored procedure dependency on legacy database without FK]( - Good Day, I need advice and solution how to get Get list Stored procedure dependency from root stored procedure because this is legacy database without FK so i can not identify relation for each table without FK. any advice or solution are welcome. thanks for advance.  SQL Server Newbies [New to SQL - difficulties in SUMing only certain rows of a column, other misc.]( - This is for SQL Server 8.0 and I'm using MySQL Workbench on this.  I have a table called 'business_ledger' and the following fields: employee_name (various string values to denote employees - not used in this query but it's part of the database), title (with values: Novice, Journeyman, Master), Earnings (various int values), certification (with […] SSRS 2016 [SSRS 2016 Mobile Reports - Parameters]( - Hello all. First of all, this is my first post here in this forum. I apologize if this question has already been asked. I am very familiar with creating standard/paginated reports in SSRS using Visual Studio. Very well versed in using parameters in reports, and creating drill downs that launch another report which passes parameter […] [How to set up user permission at report content level]( - We're planning to deploy a report to SSRS 2017 with data for multiple projects. We'd like to know if there is a way to set up permission at report content level, i.e. one user can only view data for Project-A, and the another user can only view Project-B data. I'm new to SSRS, but used […] Certification [uk training providers for dba or dev certs.]( - Hi guys, I was considering firebrand for an accelerated MCSA cert. I have years of experience, just haven't got around to getting certified yet. They are expensive but accelerated. I'm not sure I have the patience to self study for this. I feel like I would like the confidence from certification because, even though I've […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -