6 steps to a more secure SQL database (2019-10-07)

 SQLServerCentral Newsletter for October 7, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [6 steps to a more secure SQL database]( - [How to Use Parameters in PowerShell]( - [DevOps Self-Service for Databases with SQL Clone]( - [From the SQL Server Central Blogs - Download Power BI Audit Logs with the New Get-PowerBIActivityEvents cmdlet]( - [From the SQL Server Central Blogs - Debugging techniques (focusing on T-SQL)]( Question of the Day - [Default Agent Error Logs]( The Voice of the DBA  T-SQL Context Switching Studies have shown that reading someone else's code can be both stressful and time consuming. Developers that pick up unfamiliar code find themselves spending much more time comprehending the code than with more familiar projects. I think this might be one of the reasons that so many developers wan t to build things themselves, or in their team. They find the code easier to understand. This might be less of an issue with SQL code, which is often more straightforward for simple queries, but I do find that complex queries can be confusing to many developers if they didn't write the code. I'm sure plenty of people will cut and paste solutions from places like [SQLServerCentral]( without understanding how the code works, but even decoding the answer to a question you've asked can be hard. Hopefully most developers make an effort rather than coding a poorer solution or deploying code they don't understand. With staff turnover and the need for developers to work on multiple projects, this can be a drain on the efficiency of all developers. In addition, since individuals often find code easier to read in certain formats, tools like SQL Prompt can help, but there are plenty of easy ways to keep your codebase readable for staff. Dr. Greg Low has written [a nice post on standards](, which I think outlines some of the things I like to implement in a team. Having a known way to structure our code can help us communicate intuitively in a group, rather than require extra communication. Actually, I don't care what standards exist in a company. I don't have strong feelings about any particular way to structure code. I prefer spaces, but if we decide on tabs, I think that's fine. I'll with within the framework we've built. What I do think is very important is that you have some standards. They don't have to be perfect or exhaustive, but they do need to be followed. If you don't have standards, I suggest you start creating them. Add them as needed, when a developer finds a reason to make a decision, get consensus from the rest of the group and document the standard. Like other things in DevOps software development, we can grow these over time as we need them. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate Database Devops](   Featured Contents [6 steps to a more secure SQL database]( steve@thesqlguy.com from SQLServerCentral Security is often something people think about only after they have had a problem. Given that the average cost of a data breach is $3.92 million (SecurityIntelligence 2019) and ransomware attacks have increased 97% over the past 2 years (PhishMe 2019), the "if it's not broke, don't fix it" approach can clearly be catastrophic. Here […] [How to Use Parameters in PowerShell]( Additional Articles from SimpleTalk Every DBA should have basic PowerShell skills. In this article, Greg Moore explains how to write a PowerShell script that takes parameters. [DevOps Self-Service for Databases with SQL Clone]( Additional Articles from Redgate Phil Factor describes the freedom of being able to "self-serve" databases, during testing and development, and explains how it works with SQL Clone. From the SQL Server Central Blogs - [Download Power BI Audit Logs with the New Get-PowerBIActivityEvents cmdlet]( Aaron Nelson from SQLvariant Earlier this week a new version of the MicrosoftPowerBIMgmt module was made available on the PowerShell Gallery. Two new cmdlets were included in the module Remove-PowerBIReport and Get-PowerBIActivityEvents. The... From the SQL Server Central Blogs - [Debugging techniques (focusing on T-SQL)]( Kenneth.Fisher from SQLStudies tl;dr; Add PRINT and/or SELECT statements. Comment/uncomment out pieces of code to isolate problems. Change INSERT/UPDATE/DELETE statements to SELECTs. Disclaimer: ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Default Agent Error Logs How many SQL Server Agent error logs are kept by default? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Spark SQL Server 2019 will integrate with Spark. What is Spark? Answer: Spark is an analytics query engine Explanation: Spark is an Apache project that implements a unified analytics engine for large scale data processing. Ref: Apache Spark - [Discuss this question and answer on the forums](    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 [memory available but excessive workfiles still created]( - I'm analyzing PAL results during a period of poor performance and finding warnings of excessive "Workfiles Created/sec." My understanding is that these workfiles are created when data being processed is too big to fit into memory and spilled to disk. But MSSQL is only using 30GB of the 59GB max it is allocated, and the […] [Auditing selective users]( - Hi, I am putting together sql server auditing for a certain project. I create a sql server audit object (which writes audits to file). Then I create a database audit specification. First I used public as principal CREATE DATABASE AUDIT specification [DBAuditSpec] FOR SERVER AUDIT [DBAudit] ADD (SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, REFERENCES ON […] [Create index ... online=ON]( - Why is this INDEX setting not persistent ? I'm testing this on SQL2019, but I've seen the same thing on SQL2016. I run this CREATE NONCLUSTERED INDEX [ImageActID] ON [dbo].[Image]( [ImgID] ASC )WITH (SORT_IN_TEMPDB=OFF, DROP_EXISTING=ON, ONLINE=ON, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY] GO Then I refresh and right click on the index and select "Script index as […] SQL Server 2017 - Development [simple insert statement issues]( - Hello, create table #temp2(datename datetime, lastprocessed datetime DEFAULT'getdate()' NULL) insert into #temp2(datename) select distinct convert(date, c.ActualDate, 101) AS [DateName] from dim.calendar c select * from #temp2 I get below error when I run above Msg 241, Level 16, State 1, Line 20 Conversion failed when converting date and/or time from character string. SQL Server 2016 - Administration [Adding 3 rd node in Alwayson]( - Hi, We have 2 node Alwayson setup. Planning to add 3rd node. To add 3rd node, do we need to remove databases from AG and re-configure them from scratch after adding 3rd node? SQL Server 2016 - Development and T-SQL [Recommended Reading? Statements, batches, control-of-flow]( - When trying to create / save / execute a single .sql file containing multiple DDL operations, such as: use database go create procedure [procname]() as begin ------------------ end go use database go create view [viewname] as use database go create anotherview [viewname2] as ..............ETC and getting errors such as "Create View must be the only […] [Deduplicating records from monthly table backups?]( -  I have a database that has monthly backups of a table for the past five years... i.e., they copied the table at the end of the month to a table called theHugeTable_mm_yyyy. Now as you can imagine, tables are huge... and the majority of the table data didn't change from one month to the […] [@@TRANCOUNT]( - The question is: What is @@trancount after this SP is executed? I don't understand why the answer is 1. My understanding is for every BEGIN TRAN, @@TRANCOUNT is incremented by 1 and for every COMMIT, @@TRANCOUNT is decremented by 1, so why is the answer not 0? I know it has to do with the […] Administration - SQL Server 2014 [Upgrading from SQL 2008R2 SP1 to SQL 2014.]( - Hi guys, I'm planning to perform an upgrade on my current SQL server 2008R2 SP 1 to SQL Server 2014. Based on my current license limitation 2014 is the max I can go. I read KB by Microsoft, I need to be on at least SP2 for the upgrade to be successful ( if this […] Development - SQL Server 2014 [Guaranteed Varchar to Date conversion]( - I regularly load in multiple files using SSIS into a staging table and then onto another table (I call them trans as they are never fixed).  The data goes into stage as it comes out of the file or other source. This way I can always prove I took in what I was supplied. Stage_policy […] SQL Server Newbies [* to 1]( - Can I replace the * with 1 here in the below code. Will it impact the result. IF EXISTS (SELECT * FROM dbo.tbTest where Test_ID = @Test_ID) -- Like change to SELECT 1 Azure Machine Learning [Why no posts?]( - I've heard Microsoft promote their concept of the "modern data warehouse" and their tools to support machine learning. In my mind, there are at least 2 big components embedded in what they say: marketing to promote adoption of their cloud services and genuine opportunities to add value to the community. When I hear the word […] Integration Services [Automate the SSIS package to take Start and enddate]( - I have a master/parent package, that calls the child packages in SSIS. The Master package takes the startdate and enddate and get all the dates from the given Startdate and enddate and run the child packages in loop. Example: Startdate = 1/1/2019' and Enddate ='1/10/2019'. SO the Master loops through 9 times meaning 1-9th january […] PowerPivot [Power BI Data Gateway - Azure VM v On-Premises]( - When you have a SQL Server instance running in an Azure VM and you want to connect Power Bi Data Gateway to it, do you install the Data Gateway or is there another way for Power BI to get the data, considering it isn't technically "On Premises"? Suggestions [APP]( - Hi, Just a thought and sharing. Forum is great and it helps. However how about an app like an Iphone or Android app... which lets you post quick questions? I am not sure if there is already one. It will let the user post questions when there is no access to a laptop or on the […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -