SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The MI Startup Time

The Voice of the DBA

The Problems with Gods

This editorial was originally published on Sep 26, 2017. It is being re-run as Steve is out of town.

When I was learning how to work with computers early in life, I ran across various documentation and writings that would liken the root account to being a god on the system. Over the years, I've seen other articles that note will describe "God mode" in various software systems. There have even been science fiction books describing the god-like abilities of a person that obtains a privileged account on a system.

To me, this is one of those places where our industry is immature. Having an account that can perform any task on a system, with no limits, is indeed like a god. This account can do anything, which is a double edged sword. Someone can reconfigure, fix, patch, update anything to ensure the system runs well. Or they can "rm /rf" the system.

Recently Verelox had their entire system wiped out by a former administrator. There are a variety of problems with this story, not the least of which is leaving old credentials active. Mike Walsh wrote about some of the issues from a backup perspective. I would add from a security perspective that this is why an individual's credentials need to be disabled immediately, and any well known, long time passwords need to be changed. We do this in the physical world by changing locks. We need to do this in the digital work as well.

However, I see a overreaching account with unlimited privileges as fundamentally a bad idea. Sure, this makes installing software or reconfiguring our system easier, but perhaps we should be required to use separate accounts for all sorts of options. This is especially true when we build a distributed system across multiple machines. As the number of services and systems increases, the value from having one account able to accomplish every task outweighs the potential issues.

Humans make mistakes. We make inadvertent ones when we're tired or distracted. We make malicious mistakes we regret; we make emotional mistakes by overreacting to a situation. We make mistakes based on incorrect information. If we have all the power over a shared system, then we may easily make mistakes that could cause an extraordinary amount of damage.

Our modern systems should include the ability for a separation of all duties and more default accounts that we configure. At the very least we should separate administration from auditing, and perhaps security as well. A slight inconvenience during setup is worth accidental issues in the future. Having separate accounts for different functions will also help to slow down the potential problems in the future by ensuring no one user account can be used to perform every function on a platform if it's compromised.

Steve Jones - SSC Editor

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

Featured Contents

Demystifying Best Practices and Enhancing RDS security with Amazon GuardDuty

Dilip_Ravindran from SQLServerCentral

This article looks at four security best practices for protecting databases in AWS along with how GuardDuty can be used to provide additional security.

Getting Out of Character

Additional Articles from SimpleTalk

In this article, Joe Celko gives us a history of the different character sets that are used in computing and how that can pertain to your usage in relational databases. Some of these you may have never heard of!

PASS Data Community Summit 2023 registrations are open!

Additional Articles from Redgate

In 2023, connect, share & learn with like-minded peers, speakers, and industry leaders during the full week of data celebrations. Summit happens in person, from November 14th to 17th in Seattle. Check out the blog post and learn more.

From the SQL Server Central Blogs - Why Put PostgreSQL in Azure

Grant Fritchey from The Scary DBA

I’ve had people come up to me and say “PostgreSQL is open source and therefore license free. Why on earth would I put PostgreSQL in Azure?” Honestly, I think...

From the SQL Server Central Blogs - Cross-site scripting (XSS) attacks

david.bermingham from Clustering for Mere Mortals

Today, we’re diving into the world of cross-site scripting (XSS) attacks, breaking them down into three categories: Reflected XSS, Stored XSS, and DOM XSS. Let’s explore these digital threats...

SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey

SQL Server Execution Plans, Third Edition, by Grant Fritchey

Grant Fritchey from SQLServerCentral

Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The MI Startup Time
I can stop and start an Azure SQL Managed Instance. As of Mar 2023, with this feature, what is the estimated startup time for a Managed Instance that is stopped?
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)

Refreshing Modules

What does sp_refreshsqlmodule do in SQL Server 2022?

Answer: This procedure refreshes meta data for the proc/function/view passed in as a parameter

Explanation: This procedure refreshes the metadata for a non-schemabound object passed in as a parameter. This can be used with stored procedures, functions, views, DML trigers, or DDL triggers. Ref: sp_refreshsqlmodule - https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshsqlmodule-transact-sql?view=sql-server-ver16

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 2016 - Administration

Index Creation for large table failed. - I'm trying to create a clustered index on a very large heap table. Below is the script i'm using to perform the creation job. CREATE CLUSTERED INDEX [IX_Arc_tblFST_GSNID] ON [dbo].[tblFST] ( [GSN_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, DATA_COMPRESSION=PAGE) ON [ps_tbl_arctbltnr]([TNR_Date]) However the creation […]

SQL Server 2016 - Development and T-SQL

Extracting Data from XML file - Hi all, I have a XML file which consists this type of data: 12345 1 ABC XYZ TEST TYPE TEST REFNUM 2016-11-30 TESTCOMMENT TEST DESIGN TEST NAT TEST TYPE EXACT 1964-07-17 […]

How To Enumerate Hierarchical Info on SP calls - NOTE: Forgive me if I am using the wrong terminology with regards to this. \ I am trying to get a list of all The SP's (Stored Procedures) that are called by an SP. For example let's say I have the following SP's: LF5_Job1, LF5_JOb2, LF5_CalcResFees, F5_CalcCommFees & LF5_PopulatFees The SP LF5_Job contains one 'EXECUTE' […]

Administration - SQL Server 2014

DBCC CHECKDB - LATCH_EX - Hello. I posted previously about DBCC CHECKDB and Backups and changes are under review - thanks for feedback on here. This is related - but a very specific question about DBCC CHECKDB - here is what I am seeing. DBCC CHECKDB runs against Database A in SQL Instance A. On certain days of the month […]

SQL Server 2019 - Administration

What can set the recovery model of a database? - Hi I have had an incident where a database log has grown to a large size. Normally I would backup the logs however the recovery model has been set to SIMPLE. I am under the assumption that only a human can set this option. I have checked the logs and cannot find any alter database […]

Log File continue growing - Hello everyone, i read a lot about growing Log Files on this forum but can´t find a solution for my problem. I moved a database from one server to another, cause there were several performance problems on that old server. Since i moved the database the lfd file is continue growing. Database (mdf) file is […]

SQL Server 2019 - Development

how to get the max date in the table - I have a below table with the values cscode StartDate EndDate 0011 5/9/2022 10/1/2022 0011 6/17/2022 1/1/2023 0011 5/9/2022 10/1/2022 The date column is varchar. I need to take the latest mm/dd/yyyy using EndDate. For ex, in the above table I need to […]

Delete in ERREOR with an FK on the same table - Hello I do not know why my delete does not work despite I respect my FK constraint The FK constraint is based on the same table ALTER TABLE [dbo].[EVACUATION] WITH NOCHECK ADD CONSTRAINT [FK_EVA_DECH] FOREIGN KEY([COD_EXP_CHG], [COD_CEL_CHG], [NUM_DOS_CHG], [NUM_EVT_CHG]) REFERENCES [dbo].[EVACUATION] ([COD_EXP], [COD_CEL], [NUM_DOS], [NUM_EVT]) GO ALTER TABLE [dbo].[EVACUATION] CHECK CONSTRAINT [FK_EVA_DECH] GO Here is […]

SSIS Logging - Hi everyone I am having some issues with SSIS logging. I recall setting it up but I don't remember how I did it last time. It was an option in SSIS somewhere but I cannot seem to find it now. I took a look at sysssislog table and I see that it did log in […]

SQL Azure - Administration

SQL Managed Instance Accelerated Database Recovery - ADR feature is optional in Onpremise where as in SQL ManagedInstance, it is enabled by default. In the Below link performance comparison has been done by Niko Neugebauer. There's about 8% decrease in the performance for Insert/Update/Delete, when ADR is enabled. Accelerated Database Recovery so there will be atleast 8% performance degradation if we migrate […]

Query Oracle DB from Azure SQL - Folks, I'm investigating the possibility of migrating from Azure VM with SQL to Azure SQL Managed Instance but have hit a roadblock. We depend heavily on data from Oracle DB accessed through Linked Servers, both for queries and also direct imports to internal tables. My understanding is that Azure SQL does not support OLE […]

Powershell

How to Insert API data into database using Powershell - Hello, I am trying to teach myself how to INSERT data from an API into a SQL Server DB using PowerShell. For this exercise, I am using a public API which is: https://date.nager.at/api/v2/publicholidays/2020/US. What I would like to do is insert this into a table using a Stored Procedure. Below is my code. I think […]

Analysis Services

using PowerQuery with SSAS Tabular - I have a stupid question... I can do all the PowerQuery stuff in PowerBI, but can I move those transforms to SSAS Tabular somewhere, so that the transformations will be done and then just persist the results in my data warehouse/SSAS Tabular model? I know I can do this in PowerBI, but how do you […]

Strategies and Ideas

Injest user-provided Excel? - In today's episode of "Products that Should Exist (and maybe do)": Have you come across a solution (preferably a web-based portal) that allows an end user to upload an Excel file, digests the data, identifies tabular data, walks the user through column mapping to structured data, performs data validation, highlights invalid data, allows editing of […]

SQL Server 2022 - Development

Want to load data on a staging table. - In the past, I loaded data off excel, and it was difficult to manipulate in SQL. Someone suggested loading the data on a staging area next time to avoid hassles. Now, I have more of such similar data from excel to import to SQL server, and need to load on a staging table. I have […]

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.