How to (Somewhat) Increase SQL Server Security (2022-01-03)

SQLServerCentral Newsletter for January 3, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to (Somewhat) Increase SQL Server Security]( - [Adding Users to Azure SQL Databases]( - [From the SQL Server Central Blogs - Searching dbatools–#SQLNewBlogger]( - [From the SQL Server Central Blogs - On the nature of constant change]( Question of the Day - [My New Resolution]( The Voice of the DBA  Daily Coping Tip Make a resolution that you can keep this month 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. Data Cleanup The end of the year is when I do a little data cleanup. Not a lot, but some. Work slows down, with no major deadlines, so I'm able to spend a bit of time organizing myself. I usually go through my downloads and documents folders, deleting files I don't expect to use. I ensure that my jobs removing older log and temp files are running, especially on the laptops. A lot of disuse in 2021 had a few of them filling disks with more files than I had expected. In a few positions I've had as a DBA, this was a time when we'd look to archive away some older databases, and even some data. It was rare, but always invigorating because we knew queries would run faster in systems when we could archive off data. I haven't ever started with clean databases in a new year, but in a couple of jobs we'd archive off all data that was more than one or two years old into another database. It was available if someone needed it, but the main OLTP databases would contain only one or two years' worth of data, helping queries perform quicker at the start of the year. Last month I was at the [SQL Server and Azure SQL Conference](/?track=sql), and someone asked about the future plans for archive and migration of SQL Server data. The person noted they wished they could easily archive off data, specifically using a feature like [Stretch Database](. That feature wasn't cost-effective, but it might be if you could stretch to another instance rather than Azure. No good answer from Microsoft, but that is something I'd like to see. Archiving older data, often data that is less queried is a good way to speed up systems. However, doing this seamlessly, and with easy access from applications, is cumbersome. Software should make this easy. I set up a database and then point the archive process to that location. The process manages moving data from a table based on a column value I've specified. Ideally, I could also decide if I allow queries to span the two databases automatically or I require some switch to allow querying of the archived data. There are plenty of possibilities here, but I suspect we won't see any of them soon. There isn't a lot of extra money to be made by allowing customers to manage their own archival systems, and these days it seems every software vendor is trying to make money from renting features rather than selling them. Archival isn't one I see many customers willing to pay a premium for, so I suspect the solution for most of us is to write better and code and ensure queries perform well, even when we have terabytes of data. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How to (Somewhat) Increase SQL Server Security]( Hubert Sloma from SQLServerCentral Problem Some time ago Argenis Fernandez(@DBArgenis) found and described a vulnerability that allows you to get into SQL Server with 'sa' rights. This method does not require a restart of the SQL Server service or the whole machine, the condition is a local administrator account on the server. Reminder SQL Server until 2008R2: Until SQL 2008R2, […] [External Article]( [Adding Users to Azure SQL Databases]( Additional Articles from MSSQLTips.com Learn about the options to add users to Azure SQL Databases including SQL authentication logins, contained SQL users, contained Azure Active Directory users, mapped logins to users - including code examples you can copy and use immediately. [Blog Post]( From the SQL Server Central Blogs - [Searching dbatools–#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. I learned something new about dbatools. That’s using... [Blog Post]( From the SQL Server Central Blogs - [On the nature of constant change]( Randolph West from Born SQL with Randolph West Recently my spouse and I travelled to South Africa (yes, I know there’s a pandemic on) to deal with a gloomy family matter that required in-person interaction. Being an...   Question of the Day Today's question (by Steve Jones - SSC Editor):  My New Resolution  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) Hiding Your New Year's Age There is a table that has everyone's date of birth stored in it. The column is dob and it is a date type. This column has the default function applied to it to prevent non-privileged users from seeing this information. If a user without UNMASK ones this query, what do they see as the result? SELECT DATEDIFF(YEAR, dob, '2022-01-01') FROM dbo.Guests AS g WHERE g.Guestname = 'Susan' Answer: 0 Explanation: The default date value is 1900-01-01, but masking will cause the function to return a 0 here. Ref: Dynamic Data Masking - [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 [Can I safely force the plan without the table spools?]( - I have the query below that has had 2 plans since at least Dec 20th. On Dec 22nd, the plan with eager spool operators began to be used orders of magnitude more than the other plan. Those operators show up preceding non-clustered index inserts. For some reason, the other plan shows no indication of updating […] SQL Server 2017 - Development [Multiple Date rows into One single Date row(date range)]( - Dear ALL I have a requirement of converting different date ranges into one single row, group by EmployeeNr. You can exclude CompensationNr and Payment Date columns, while writing the query. Following is an example of table and result i want it to be. Thanks. Development - SQL Server 2014 [How to remove additional element from Nested XML]( - Hey everyone. I'm a bit new to XML and had an issue with some formatting. I'm nesting XML queries and it creates an additional element which I need removed. Here is a sample of what I'm doing. select PrintOrderNo as "@PrintOrderID" ,PayGroupID as "@PayGroup" ,( select LineNumber as "@DetailNo" ,CompensationDescription as "@Description" FROM Table1 […] SQL 2012 - General [How to get data from table Compliance Data Based on PartId and ComplianceTypeId?]( - I work on SQL server 2014 I need to get data from compliance data table horizontally . based on part id and compliance type Id every part id will have one row per 3 compliance type every row per part will have 3 compliance type id 1,2,11 if part not have 3 compliance then it […] [Encrypting SQL 2012]( - I need some assistance, please. Now that TDE has blown up in our faces with SQL 2012, we are being pushed to use "native SQL encrypti on" on our databases to ensure the data at rest is encrypted. This while we're in the middle of a migration to new .  Does anyone know what […] SQL Server 2019 - Administration [Install SSIS]( - Hi I am getting this error when i try to install SSIS: MainViewModel.OnBundleAction: Bundle action failed: The requested metafile operation is not supported (0x800707D3) I am downloading and installing this file: I already installed Visual studio 2022 and I am running SQL Server 2019. Do you know how I can fix this error message? […] [Creating master key on test server in order to install backup encryption]( - We encrypt our database backups. We've installed some new client servers and I was able to create the encryption certificate on Production and Staging however when I try to create the master key on our test server I get the following errror: Msg 33094, Level 16, State 9, Line 1 An error occurred during Service […] [polybase]( - Does big data cluster has any relation with SQL Server 2019 polybase? SQL Server 2019 - Development [CASE statement and partition by]( - Hello everyone, I have a question if someone may know how to adjust the code correctly. I need just to edit the last outer SELECT statement. I need to change the partition by DMDUNIT, I need outer CASE statement to look at 2 columns: 1) Start Date - if we have 3 distinct weeks worth […] [Using OPENDATASOURCE as an ad hoc Linked Server]( -  I'm not sure if this should be posted here or not, but I will try. We have a configuration where multiple customer databases are distributed across several servers. On occasion (not frequently), it is necessary to compare data between two databases on the same table utilized by the application. Because there are several Servers […] [BCP adding carriage return/new line every 2034 characters]( - I have an SQL query that creates a JSON output. The query works properly and gives me the output I expect when I manually run it via SSMS. When I run it in a BCP command from an SP, the BCP utility is adding carriage returns and new lines every 2034 characters. I can open […] [Interpolation/Extrapolation]( - Hi everyone I am trying to use linear and cubic spline interpolation/extrapolation to fill in missing rates. I have never done this before in SQL. What is the most efficient way to do this? Thank you [Show data that takes into consideration data of consecutive weeks]( - Hello everyone, I have such a situation. I need to only show items that have had 3 consecutive weeks of over or under forecast or Bias. I am trying to understand how I can i can filter data taking into consideration consecutive weeks. If someone may have any ideas, I will appreciate...  SELECT COALESCE(f.[LOC], a.[LOC]) […] General Cloud Computing Questions [Hi. Most cheap PERSONAL cloud computer? At least 8gb RAM]( - I am testing Vagon.io, but it is somewhat expensive for me. Does anyone know of any other cheap alternative? I think the best thing would be to pay per subscription and not for what you use. I would be willing to pay something like 7 dollars one week and be able to use as many […] Integration Services [Script Task issue during SSIS migration from 2016 to 2019]( - SSIS migration from 2016 to 2019 We migrated our SSIS environment from 2016 to 2019. We have several SSIS packages that have vb script tasks. Once we migrated we tested some of these packages and they worked fine. However there are some older packages that are failing when they run. The initial error was : […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -