Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate SQL Prompt
The Voice of the DBA
 

Daily Coping Tip

Get outside today and give your mind and body a natural boost

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

The Danger of xp_cmdshell

This editorial was originally published on 16 Sep 2016. It is being republished as Steve is on holiday, but if you have changed your mind, leave a comment in the discussion.

Securing a computer is a challenge. There are all sorts of potential issues in every platform, and ensuring safety for your data can be less a reflection of your ability and more the good fortune there isn't a focused effort to attack your systems. However, we certainly also face issues with inside users, many of which may make mistakes that are accidental more than malicious. It's for these reasons that we look for secure by default applications and a reduced surface area for any system.

Many people refuse to turn on xp_cmdshell as an option for scripting in SQL Server. This is disabled by default, and quite a few DBAs are glad of this setting. However, there are plenty of people that think xp_cmdshell isn't a big security risk. There are certainly ways to mitigate the usage by non-privileged users, and this can be a tool that is very handy for accomplishing work without a lot of development time.

This week, as security issues become more important to us all, I'm curious how you feel.

Do you think xp_cmdshell is dangerous?

I have to admit that I'm torn. I don't think this inherently dangerous. It does open up some attack vectors, but the last few versions of SQL Server have allowed some limitations, so I would enable this if needed to solve some issues without too many concerns. However, I wonder if many of you feel the same way.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents

Creating Subtotals and Totals in Aggregated Queries

Adam Aspin from SQLServerCentral.com

Grouping Sets are an effective way to add subtotals to aggregated queries. T His article explains how.

Database Development with GitHub

Additional Articles from Redgate

How can you use GitHub to do team-based database development? This article proposes a process that splits development work into task-based GitHub branches, incorporates daily database builds and integration testing, and using Redgate tools to automate tasks such as provisioning, database scripting, and testing.

Searching and Finding a String Value in all Columns in a SQL Server Table

Additional Articles from MSSQLTips.com

Sometimes there is a need to find if a string value exists in any column in your table. This script will help you find all occurrences in all columns in the table.

From the SQL Server Central Blogs - Azure SQL Offers Manual Failover for PaaS Resources

John Morehouse from John Morehouse | Sqlrus.com

Sometime having the right command in place opens up new doors to test things, like a failover for example.  In this post we will take a look at a...

From the SQL Server Central Blogs - Git Tricks–Getting a New Remote Branch–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is probably more for me than anyone,...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Creating a new branch in Git

I am on the main branch of my git repository. 
$ git branch
* main
I want to start work on a feature branch, and need to create and checkout the feature/stevesalesorder branch. What command does this easily?

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)

The Last Compile Time

I have a stored procedure, and I am trying to determine when it was last compiled for a plan. Where can I find this information?

Answer: sys.dm_exec_procedure_stats

Explanation: The cached_time column from sys.dm_exec_procedure_stats will tell you when this particular plan was compiled and added to the cache. Ref: sys.dm_exec_procedure_stats - https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql?view=sql-server-ver15

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
Restart SQL server from CMD - We have a issue in SQL Server 2017 (RTM-CU21) with SSISDB when a server restart happens SQL server will go into a stopped state and I cannot restart it from the configuration manager have to run the following command from cmd to start the service Net Start MSSQLSERVER /T902   how can I make this […]
Replication issue - my table is getting replicated from server 1 to server 2. I got a request to delete the records in table in server 1 and insert new set of records.  does replication take care of the records in server 2 or should i do something to make both in sync.
Missing constraint after backup and restore - Hi we have this procedure to restore a db: On the prod db Generate a script of a db and than click on Tables after that click on advanced in Types of data to script select schema and data. We launch a ps script that use sqlcmd -E -S -Q on the db and than […]
script d\'olla in azure - hello, how to configure the olla script to backup on Azure Ola Hallengren's maintenance solution i want to make backup database to blob azure   thanks
SQL Server 2017 - Development
Cannot restore DB using T-SQL script - Hello, I had a database called Training_01 and did a backup of it. It is now in 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\RodierTrainingFood.bak' I did a File liste of it and saw that the logical name was OS_OS_Training_01 so it is a different name than the name of the original database. The goal is to know how […]
sqlcmd: The size (65535) given to the column 'x' exceeds the maximum allowed - Hello I am trying to copy SQL Server table with varchar(max) to Amazon Redshift. There is no direct connection, I have to save data to file and load data from the file. The maximum length for VARCHAR in Redshift is 65535 bytes SELECT LEFT(CAST('ABC' AS VARCHAR(MAX)), 65535) AS Col; sqlcmd -i "select.sql" -o "out.txt" Msg […]
SQL Server 2016 - Administration
SCOM/SCCM alert when Always On High Availability dashboard is green - Hello experts, Does anyone know why SCOM (or is it SCCM?) would send the following alert when everything is OK and green in the Always On High Availability > Show Dashboard? The Metric is: Microsoft.SQLServer.Windows.Monitor.Database.DBStatus (UnitMonitor) https://systemcenter.wiki/?GetElement=Microsoft.SQLServer.Windows.Monitor.Database.DBStatus&Type=UnitMonitor&ManagementPack=Microsoft.SQLServer.Windows.Monitoring&Version=7.0.0.0 MSSQL on Windows: Database is in offline/recovery pending/suspect/emergency state The databases in each AG are also online and […]
SQL Server 2016 - Development and T-SQL
Storing File in SQL database - Best option - What do I tell a .net developer is the best approach for storing various file type in a SQL db? PDF, TIF, JPG...
sql server 2016 performance tuning - I have opportunity to tune below monster query. Is there anything right off the bat you guys think I should change? DECLARE @pDate date SELECT @pDate = '20200910'   DECLARE @dt6M date = (SELECT DATEADD(MONTH, -6, @pDate)) DECLARE @dt3M date = (SELECT DATEADD(month, -3, @pDate)) IF OBJECT_ID('tempdb..#tmpMasters0') IS NOT NULL DROP TABLE #tmpMasters0 CREATE TABLE […]
Administration - SQL Server 2014
Migration of Server - We are looking to move the SQL server to different datacenter. Since it's a standalone instance so it can be moved using V2V or other different options. However, the IP address might change after the move? I am thinking changing from configuration manager would be suffice the IP Address Listened by the SQL Server. Do […]
Transaction log used space - It's a little weird for me. I have a database (AdventureWorks2014; SQL Server 2014) in full recovery model. Executing this script 3 times: declare @i int = 0 declare @date datetime while @i <= 1000 begin set @date = getdate() begin tran update Production.TransactionHistory set TransactionDate = getdate(), ActualCost = @i + TransactionID where TransactionID […]
SQL Server AMD Patch applied to INTEL Server - I have a situation (not me personally but a client) where SQL Server 2014 SP2 on an INTEL Server has been upgraded to SP3 and then CU4 for SP3, but the patch files were AMD. After this the latest Security Patch was also applied (SQLServer2014-KB4535288-x86.exe) which doesn’t have an infrastructure designation. I would have thought […]
Development - SQL Server 2014
Insert Trigger - Hi All, I have table A that has 2 columns(col 1,col2). I would like a create trigger that collects all duplicate inserts.For example ,when i insert new records that both col 1and col2 exists on Table A ,then insert that record into Table Audit as well. Here what came up with ,but logic is not […]
Reporting Services
SSRS and SharePoint - We currently have SSRS 2017 and are looking at adding SharePoint to our environment.  The question has come up as to whether we should implement SharePoint with Reporting Services or without and keep our SSRS 2017 separate as it is. I'm just starting out with Reporting Services and have no experience with SharePoint.  I'm not […]
SSRS Query Designer not showing newly added measures - We have recently added new measures to our existing cube (that was originally developed in SSAS 2012 and then we stood up SSAS 2019) in dev and test. When I go into my SSRS project using VS 2015, then the query designer and refresh my connection to SSAS 2019, the measures that were developed in […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -