Getting locked out of SQL Server: Cannot open user default database. Login Failed. (2022-01-22 DBW)

Database Weekly for January 22, 2022 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  Reusing Tools for New Purposes One of the things you learn early on in programming is that you ought to reuse code whenever possible. This often means refactoring code into functions or methods. This ensures that the code is more easily maintained and that the knowledge and work of solving the problem is reused in many places. In database code we don't do this too often. We certainly can reuse some code by encapsulating it in a view, but this often brings performance penalties. We often don't want to reuse code in stored procedures and functions as embedding this into queries can cause lots of issues. In fact, it seems that much of the way databases optimize query performance isn't that amenable to reusing code. That being said, I ran into an interesting case recently when thinking about maintenance in the cloud. Someone was asking about SQL Agent and the lack of support for it in many PaaS database systems. I understand that, and while there are some ways to do this, they feel complex compared to using a SQL Agent on a local instance, which is usually easy to set up and readily available. One of the speakers taking questions mentioned that they user shouldn't forget about Azure Data Factory as an automation agent. That caught my attention as I hadn't thought about it before. This week, there was [a blog on that very topic]( and I read through it to see what I thought. While this isn't as easy as SQL Agent, it does seem to be easier than [Azure Automation](, and likely more familiar than [elastic jobs](. That's if you already have ADF running in pipelines. In many ways, this feels like using the maintenance plans in SQL Server, though just the call a stored procedure task. Since many people use a solution [like Ola's](, this is very easy to implement. I like that this pattern reuses skills and a system that you may already be using, transferring the skills from one area (ETL) to another (administration). This might not seem like much, but limiting the tools and technology, reduces complexity and means that each person needs to know less to support your environment. I'm a fan of code re-use, outside of T-SQL), and I think reusing other technology systems, where appropriate, is a good idea. Steve Jones - SSC Editor [Join the debate, and respond to the editorial on the forums](  The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Vendors/3rd Party Products [Help shape the future of Flyway]( We’ve got some big plans for this year, and are looking to talk to people who use Flyway in a professional capacity. If you are willing to talk to us about how you first started using Flyway, then get in touch. Of course we’ll also look to say thank you for your time in our usual fashion. [A Programmer’s Guide to Flyway Configuration]( Phil Factor offers a programmer's guide to Flyway's configuration settings, explaining the different categories of parameters, the role of each of parameter within each category, and how to exploit Flyway's multi-level configuration file system. AI/Machine Learning/Cognitive Services [Scheduling Azure ML Compute Instances]( From 36 Chambers – The Legendary Journeys Not too long ago, I mentioned that compute instances in Azure Machine Learning are lacking one critical thing: auto-stop functionality. The single biggest problem I have with compute instances... [Low-Code ML: Batch Scoring]( From 36 Chambers – The Legendary Journeys This is part six in a series on low-code machine l... Administration of SQL Server [SQL Server Memory Limits]( From Steve Stedman When working on different client servers, we often times get asked, why all their memory is not being used, and often times we are hitting the memory limits for... [The SQL Server Transaction Log, Part 1: Logging Basics]( From SQLPerformance.com Paul Randal starts a new blog series to dive deep into every aspect of the SQL Server transaction log. The post The SQL Server Transaction Log, Part 1: Logging Basics... [How to tell if Change Data Capture (CDC) is working as expected]( From SQLServerCentral Blogs I’m not sure how many of you use Change Data Cap... [Add Last Step to SQL Agent Jobs for Failure Notification]( From MSSQL Tips In this article we look at how to automatically add a final custom notification step to all existing SQL Agent jobs using PowerShell by reading and adjusting jobs steps... [Getting locked out of SQL Server: Cannot open user default database. Login Failed.]( I love playing around with test instances. Recently I've been doing a lot of preparation for some forthcoming blog posts and when trying to log into SSMS this morning I received the following error message: [Checkpoint Analysis: Your Exclusive How-To with XEvents]( From SQLServerCentral Blogs Checkpoints are essential in SQL Server to help wi... [Detect Low PAGE Compression Success Rates in all Databases]( From Eitan Blumin Thanks to Paul Randal and his recently published b... [Just Enable Indirect Checkpoints on All Databases, Please]( From SQLBlog.org In this tip I show how indirect checkpoints can help improve performance and stability of more than just checkpoint operations themselves, and stifle some (but not all) problems. I... [Backup Retention – Part 2 of 3]( From Steve Stedman Click HERE to read Backup Retention Part 1. In part 1 I spoke about why you would want to restore from backups and some other reasons to have an... [How to Benchmark SQL Server for a hardware migration]( From SQLServer-DBA.Com Benchmarking a SQL Server environment for a hardware migration has a few challenges [Measure the Effect of “Cost Threshold for Parallelism”]( From Michael J. Swart The configuration setting cost threshold for parallelism has a default value of 5. As a default value, it’s probably too low and should be raised. But what benefit are... Azure CosmosDB [CosmosDB and Consistency]( From SQLServerCentral Blogs I was doing a little work with CosmosDB recently, ... Azure SQL [Auto-Failover Groups for Azure SQL Hyperscale Tier]( I am not sure if many of my readers are aware but I am a fan of the work that Davide Mauri, and his team at Microsoft do on the Azure SQL Hyperscale team. I have had the pleasure of working with Davide through the MVP program and he is truly one of the more beautiful minds at Microsoft. Therefore, I am always excited when his team releases a new feature. Azure SQL Managed Instance [How-to improve data loading performance on SQL Managed Instance with General Purpose service tier]( In this blog post we shall consider some of the strategies for improving data loading performance in Azure SQL Managed Instance. Azure Synapse (SQL Data Warehouse and Data Lake) [LEVERAGING AZURE SYNAPSE ANALYTICS SERVICE INTEGRATIONS]( This one is obvious, but hear me out. Every Synapse implementer knows that Synapse works fully integrated with the assigned Azure Data Lake storage account that’s set as its primary storage. However, Synapse–whether primary or not– is very easy to integrate with any storage account [Introducing Map Data preview for Synapse]( This week, we are excited to announce the public preview for Map Data, a new feature for Azure Synapse Analytics and Database Templates! The Map Data tool is a guided process to help users create ETL mappings and mapping data flows from their source data to Synapse lake database tables without writing code. [Implement Dimension Denormalization Using Synapse Mapping Data Flow]( From MSSQL Tips In this tip we are going to discuss dimensional denormalization when working with Azure Synapse Analytics to reduce joins and improve performance. Big Data [Data lakes take on big data]( A data lake is a scalable data storage repository that can ingest large amounts of raw data and make it available on-demand. Robert Sheldon explains the benefits and challenges of data lakes. Career, Employment, and Certifications [Do You Need Faster Ethernet at Home?]( From Glenn Berry Introduction Do you need faster Ethernet at home? The obvious answer is that it “it depends”. In this case, it depends on several factors. First, just to be clear,... Computing in the Cloud (Azure, Google, AWS) [How to Use the Terraform Azure Provider to Deploy Cloud Resources]( From Petri IT Knowledgebase If you plan to manage and work with Azure along with Terraform, using the Azure provider is a must. The Terraform Azure Provider lets you interact with the many resources supported by Microsoft Azure.... [Deploying Azure Container Instances using YAML]( From SQLShack The Azure Container Instances provides a flexible solution for implementing container-based images in Azure infrastructure quickly. It does not require building the virtual machine, installing applications like Microsoft SQL... [Azure DB for MySQL – Single server vs Flexible]( From SQLServerCentral Blogs When you start the build process for MySQL you will be shown the below screen, the question is what option would you select? It really does depend; I don’t... The... Conferences, Classes, Events, and Webinars [Upcoming ADF and SSIS Training]( From AndyLeonard.blog() I am honored to announce the next round of live an... DMO/SMO/Powershell [Using dbachecks for CIS Security Checks]( From SQLServerCentral Blogs Well back at the end of 2019 I finished writing most of the checks related to the CIS Center for Internet Security requirements. I have yet to write a... The... [Fun With PowerShell Objects – Adding Methods to PSCustomObject]( From Arcane Code Introduction In the previous installment of this s... [Getting the SQL Server Product Key via Powershell]( From 36 Chambers – The Legendary Journeys I was working on an upgrade recently, trying to move from SQL Server 2016 to SQL Server 2019. I wanted to perform an upgrade in-place on an Azure VM,... [Behind the PowerShell Pipeline]( From The lonely Administrator Last week I made some passing references on Twitter to a new project I am working on this year. I thought I’d take a little time to explain it... Data Visualisation [What Is Data Visualization? Benefits, Types & Best Practices]( From Past News - RSS Feeds At the core of digital transformation lies data de... ETL/SSIS/Azure Data Factory/Biml [Trusting the Fine Print or connecting Azure Data Factory with Salesforce]( From SQLServerCentral Blogs (2022-Jan-19) David Eldersveld once said that a “cool solution is a completely insecure solution” - David Eldersveld on Twitter. That puzzled me, but only for a little while, since I... The... [Azure Data Integration Pipelines – Advanced Design and Delivery]( From Paul Andrew SQLBits 2022 – Full Day of Training 9th March – Online or In Person at the London Excel In this full day training data session, we’ll quickly cover... [One Way to Create an SSIS Catalog Read-Only Database Role]( From AndyLeonard.blog() This post is part of a series about SSIS Catalog Security. The other posts in this series are: One Way to Set SSIS Catalog Security One Way to Script... [SSIS Dynamic File Name]( In a recent project we needed to create an SSIS package to export data from a SQL Server table to a CSV file. One of the requirements was to dynamically append the date to the file name each time a file was generated. [Automating Azure SQL DB index and statistics maintenance using Azure Data Factory]( As we know, it's crucial that you run Database maintenance regularly in order to keep your database performance up with the latest statistics and healthy indexes, here we provide another way to schedule this job using Azure Data Factory; this can be done on a scheduled time interval weekly/bi-weekly/monthly. [Install and Configure SSIS Framework File Community Edition]( From AndyLeonard.blog() I recently released SSIS Framework File Community ... [How to Use the SSIS Profiling Output File to Generate SQL Server Sparse Columns Script]( From MSSQL Tips In this article we look at how to identify columns that are candidates to be SQL Server sparse columns as well as automatically generating the scripts to make the... Hardware [Intel Expands 12th Gen Core to Ultraportable Laptops, from 5-cores at 9 W to 14-cores at 28 W]( From AnAndTech Over the years Intel has prided itself on its ability to provide processors that fit into the ultraportable, professional market. We’re talking thin and light designs with obscene levels... MDX/DAX [GEOMEAN, GEOMEANX – DAX Guide]( From Sqlbi GEOMEAN: Returns geometric mean of given column reference. GEOMEANX: Returns geometric mean of an expression values in a table. [DAX table functions for paginated reports: Part 1]( From Simple Talk Several DAX table functions can be used for paginated reports when you want all the data, not a summary. Adam Aspin explains how to use DAX table functions for... [SQRT, POWER – DAX Guide]( From Sqlbi SQRT: Returns the square root of a number. https:/... [Using ALLSELECTED in composite models]( From Sqlbi Using ALLSELECTED with no arguments in a remote model later used in a composite model might produce unexpected results. In this video, we explain why ALLSELECTED requires special attention. [Auto-Scaling for [DAX] KPI Strings?!?]( From Havens Consulting Video by: Reid Havens Learn how to apply some [DAX] wizardry to intelligently format string values when concatenating KPI values together. RELATED CONTENT Kane Snyder's Calculation Group PresentationJeff Weir's Twitter... Performance Tuning SQL Server [Software Vendor Mistakes With SQL Server: Indexing #Temp Tables Incorrectly]( From Erik Darling Data Understanding I’ve covered this sort of information in previous posts, like: What Kind Of Indexes Can You Create On Temporary Objects? When Should You Index Temp Tables? But some points should be made... [Software Vendor Mistakes With SQL Server: Misusing Common Table Expressions]( From Erik Darling Data Stinko In this post, I’m gonna show you how stringing together a bunch of CTEs can cause performance problems with one of my paid training videos. If you like it,... [Two Clustered Indexes?]( From Scary DBA (Grant Fritchey) Everyone knows that you only get a single clustered index, right? Wouldn’t it be great though if you could have two clustered indexes? Well, you can. Sort of. Let’s... [Software Vendor Mistakes With SQL Server: Not Getting Parallel Inserts Into #Temp Tables]( From Erik Darling Data Do That, But Faster Serial zones in parallel plans can leave a lot of performance on the table. One of the best ways to see that is with insert queries... Polybase/HDInsight [Best Way of Optimization: Bucketing in Hive]( Apache Hive is an open-source data warehouse system used to query and analyze large datasets. Data in Apache Hive can be categorized into the following three parts : PostgreSQL [Creating a Database and Tables in PostgreSQL: Learning PostgreSQL with Grant]( The syntax for creating databases and tables in PostgreSQL is similar to other RDBMS. In this article, Grant Fritchey shows how to create databases and tables. PowerPivot/PowerQuery/PowerBI [Power BI Feature Update: Page and Bookmark Navigators]( Speaking of ringing in the new, with all the hustle and bustle associated with the holiday season and the excitement of welcoming the new year, did anybody happen to notice the new feature quietly rolled out in Power Bi back in November to take the edge off creating and maintaining custom pages and bookmarks? [How to Generate your Power BI Reports with Power Automate]( One of our fundamental principles of architectural design is simplicity. At Coeo, we have helped many customers implement Power BI Premium and deprecate their Analysis Services and Reporting Services infrastructure. While deploying SSRS reports to Power BI as Paginated Reports is a fantastic Software-as-a-Service solution, how do we replace our on-premises data-driven SSRS subscriptions? [How we optimize Power Query and Power BI Dataflows]( From Guy in a Cube Let's go into Adam's mind as he optimizes Power Query to make some Power BI Dataflows run faster to avoid resource usage. The result is INSANE AMAZING! Power Query... [Getting The IDs Of All Visuals In A Power BI Report Page Using The Power BI Embedded Analytics Playground]( From Chris Webb's BI Blog Log Analytics contains information on the dataset,... Product Upgrades and Releases [General availability: Azure DevOps updates]( From Azure Updates You can now see multiple improvements to the Copy Dashboard preview and try out a new feature which automatically retry a task in a pipeline when it fails. [Released: Microsoft.Data.SqlClient 4.0.1]( From MS SQL Server Blog We have released an update to Microsoft.Data.SqlClient, version 4.0.1. The update addresses several issues that are important to our customers.  Updates in Microsoft.Data.SqlClient 4.0.1 include:  Fixed Fixed Kerberos authentication failure when using... [SSIS Catalog Browser v0.9.9.3]( From AndyLeonard.blog() I am happy to announce the availability of SSIS Catalog Browser v0.9.9.3. SSIS Catalog Browser is free. You are welcome. This post is part of a series about SSIS... [Updated First Responder Kit and Consultant Toolkit for January 2022]( From Brent Ozar Unlimited In this release, sp_Blitz shows some information about clusters and AGs, sp_AllNightLog and sp_DatabaseRestore get some love, and more. To get the new version: Download the updated FirstResponderKit.zip Azure... [Public preview: Support for managed identity in Azure Cache for Redis]( From Azure Updates Connect Azure Cache for Redis to storage accounts via managed identity. [Azure SQL—General availability updates for late January 2022]( From Azure Updates General Availability enhancements and updates released for Azure SQL [General availability: One-minute frequency log alerts]( From Azure Updates With 1-minute frequency log alerts, query can be evaluated every minute for faster time to fire. [General availability: FIPS enabled node pool in Azure Kubernetes]( From Azure Updates You can now create FIPS 140-2 enabled node pools in AKS [General availability: Ultra disks support on AKS]( From Azure Updates Data intensive AKS workloads can now take advantage of Azure ultra disks. [Generally available: Azure Database for PostgreSQL – Flexible Server in two new regions]( From Azure Updates Provision your Azure Database for MySQL - Flexible Server today in the China East 2 and China North 2 regions. [Generally available: Azure Database for PostgreSQL – Hyperscale (Citus): New certifications]( From Azure Updates New compliance certifications are now available on Azure Database for PostgreSQL – Hyperscale (Citus), a managed service running the open-source Postgres database on Azure. Python [Getting Started with Graph Analysis in NetworkX]( One of the constants security professionals deal with is a high volume of alerts. Even after filtering out the noise and getting to the important events, that number is still usually much larger than what security teams can address in a normal shift. [Profiling Python Code]( Profiling is a technique to figure out how time is spent in a program. With this statistics, we can find the “hot spot” of a program and think about ways of improvement. Sometimes, hot spot in unexpected location may hint a bug in the program as well. R Language [Identifying R Functions & Packages Used in GitHub Repos]( TLDR: funspotr provides helpers for spotting the functions and packages in R and Rmarkdown files and associated github repositories. See Examples for catalogues of the functions/packages used in posts by Julia Silge, David Robinson, and others. [Playing Wordle in R]( From Statistical Odds & Ends The game Wordle has taken the world (or at least my facebook feed) by storm. It’s a really simple word game that’s a lot like the classic Mastermind. Here... Replication [Primary Key Violation in Bidirectional Replication between SQL Managed Instance and SQL server on VM]( Bidirectional transnational replication is a specific transnational replication topology that allows two servers to exchange changes with each other: each server publishes data and then subscribes to a publication with the same data from the other server. Security News and Issues [Automating Response Is a Marathon, Not a Sprint]( From Dark Reading: Dark Reading News Analysis Organizations should balance process automation and human interaction to meet their unique security requirements. [Maryland Dept. of Health Responds to Ransomware Attack]( From Dark Reading: Dark Reading News Analysis An attack discovered on Dec. 4, 2021 forced the Ma... [Microsoft Releases Out-Of-Band Windows Server 2019 Update to Address Hyper-V, ReFS and VPN Issues]( From Petri IT Knowledgebase Microsoft has started rolling out an out-of-band update for Windows Server 2019. The same KB5010791 patch addresses a couple of critical issues introduced with January 2021 Patch Tuesday updates.... [An Overlooked Cybersecurity Threat: 5G]( From Past News - RSS Feeds 5G is taking the world by storm for its notable speed and bandwidth strength. It supersedes 3G, which gained popularity in the early 2000s after the iPhone 3G was... T-SQL [When to use CHAR, VARCHAR, or VARCHAR(MAX)]( From Simple Talk It’s important to choose the right datatypes when designing a database. Greg Larsen explains the differences between CHAR, VARCHAR and VARCHAR(MAX).… The post When to use CHAR, VARCHAR, or VARCHAR(MAX)... [An overview of DIFFERENCE and SOUNDEX SQL functions]( From SQLShack This article gives an overview of DIFFERENCE and SOUNDEX SQL Server built-in system functions. It explains how to use those functions and how do they work. Introduction Soundex is... [Use MACD to Predict When to Buy and Sell Securities with T-SQL]( From MSSQL Tips In this article we look at using TSQL to create MACD moving average convergence/divergence indicators to predict buy and sell indicators for stocks. [SQL percentage calculation examples in SQL Server]( From SQLShack In this article, you will see the different ways to calculate SQL percentage between multiple columns and rows. You will also see how to calculate SQL percentages for numeric... Testing Software [Crowdsourcing quality: Can the hive mind/herd mentality help you in software testing?]( Crowdsourcing is one way to get a job done. In this article, Devyani Borade describes crowdsourcing quality testing of software. Virtualization and Containers/Kubernetes [4 Alternatives to Kubernetes Container Orchestrator]( From IT Pro - Microsoft Windows Information, Solutions, Tools While nearly nine out of 10 organizations use Kube...  [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. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -