Tag Archives: tsql2sday

For this T-SQL Tuesday we are asked to look into our crystal SQL Server ball and predict what will be happening at the time of T-SQL Tuesday #200. I went to the garage, dug that thing out, cleaned it up, and boy it had a lot to say!

Assuming we’re all friends here, and there is some fun to be had with this…

T-SQL and JSON had a baby. All queries in SSMS resemble a hybrid of the two languages.

SSDT has been replaced by VSDT. Nothing has really changed but the acronym. As always, you can still expect some things to break when you do updates.

Microsoft bought NHibernate. You still have all the same issues as before but now you post them to Microsoft Party (it replaced Collaborate…after that replaced Connect) and actually watch them not get fixed. And you can’t post work-arounds in MS Party (so it’s not much of a party).

MS NHibernate still generates SQL queries that are long and redundant, but it’s not handling the TSQL-JSON baby very well. So there’s that.

Microsoft acquired ActiveBatch and it is now called SQL Server Batch and has replaced SQL Server Agent for scheduling jobs in SQL Server 2026. Consequently, companies have been reluctant to upgrade from SQL Server 2023 (especially the ones that have used ActiveBatch).

For the companies that are upgrading, they have found that calling PowerShell scripts from scheduled tasks to be a good way to bypass using SQL Server Batch. Increase the in the demand for DBA’s with extensive PowerShell experience sky rockets!

The rumors back in 2018 proved to be unfounded – DBA’s are still in high demand. All the talk of SQL Server tuning itself turned out to be DTA 2.0.

Microsoft brought back the MCM. And then killed it again the next year.

Azure has been replaced by Rainbow. Data is no longer in the “cloud” – it is in “rainbows.” Pricing is based on the colors of the rainbow and the color names are garnet, citron, lemon, lime, azure, and violet.

PASS still exists. Due to some bylaw changes, elections have not been held since 2019. Grant Fritchey [B|T] is still president and attends meetings remotely from his nursing home.

Just kidding – Grant’s not in a nursing home. That’s just where he says he is. There was some backlash when PASS did away with SQL Saturday events. Grant’s really in witness protection and goes by the name Thomas LaRock [B|T].

Thanks to Adam Machanic [B|T] for hosting the T-SQL Tuesday this month, and for coming up with this whole thing to inspire all of us to write more and continue to share knowledge. While there was absolutely no knowledge in this post, I do hope that I got a giggle from at least one person.

Since I have not blogged in a while, and I saw it was T-SQL Tuesday, I thought I would participate in this FOR THE FIRST TIME EVER! This month SQLBalls asks us if our SQL Servers are on the naughty or nice list. Since I have recently transitioned to a new role, I am still getting familiar with the servers and the environments I am working with. The servers of my past are but a memory, but a fresh one. I cannot even begin to think of all the different “naughty” things that were done on those servers, and how some days it felt like a losing battle. How can you fight a security or code change when you have been over ridden by management, only to have that change come back and bite you weeks or months later? The “I told you so” you might feel like popping off will fall on deaf ears and you will just be stuck fixing the problem…until it gets to be too much and you decide it is time for action.

I have done this before. The day had been a long one and I thought I was finally going home when I got drug into a call on a data issue – something was changed that shouldn’t have been. When and by whom? I didn’t know or have any way of finding this information (days later when I tried to get a backup that was several months old to validate this data from when it was deployed; there was no backup – all all – but that is a completely different issue for another time). I had my suspicions but no proof. Could have been a naughty developer elf logging in with an elevated SQL account they knew the password for. Changing the password? “Out of the question” they say. It is everywhere.

If this were the only event that had happened THAT DAY. It wasn’t. This one was production (hence the conference call). The others (yes, more than one) were pre-production. Messes that had to be cleaned up because the developer elves thought they knew better than the DBA and that they could do it on their own. This production issue was the last straw. Elves were running a muck and had to be reigned in, and they weren’t going to like it.

All the elevated permissions in the pre-production servers – gone. I didn’t care if it was a DEV server. Am I the meanest DBA in the world? So says some. Scrooge? Well, if you are into name calling and want to go there, then ok, but I get to call you names too. In this case I did not care – I was fixing things that were only broken because someone abused a privilege. It should also be said that there was some relation in the names off all the tables involved with all the issues that occurred on this day.

If I had to wag my finger and any naughty part of the SQL Server instances it would have to be at security…and I am partially to blame. It can be difficult to keep up with all the changes that happen across a large environment when it comes to assigning permissions, and if you have more than one DBA, the situation is compounded by the fact that you might not always know what the other is doing and vice versa. They might grant something that you would otherwise veto for cause. You might take care of a permissions issue one way when they would handle it differently.

While I worked on some Powershell code to pull back users from specific AD groups and incorporate alerts for some of those groups, sadly the bandwidth was not there to fully roll this out. I did however create some triggers that would send email alerts when a change was made at the server and database levels, and I made them nameless and encrypted.

SET@mailbody=&#039;The following change has been made to the &#039;+@database+&#039; database on &#039;+@servername+&#039;:&#039;+CHAR(13)+CHAR(10)

+&#039;Change Made By: &#039;+@adding_user+CHAR(13)+CHAR(10)

+&#039;Event Type: &#039;+@event_type+CHAR(13)+CHAR(10)

+&#039;Server Name: &#039;+@servername+CHAR(13)+CHAR(10)

+&#039;Database Name: &#039;+@database+CHAR(13)+CHAR(10)

+&#039;Date/Time: &#039;+@datetime+CHAR(13)+CHAR(10)

+&#039;SQL Statement: &#039;+@command+CHAR(13)+CHAR(10)

EXEC msdb.dbo.sp_send_dbmail

@profile_name=&#039;DBMailProfile&#039;,

@recipients=&#039;alert@yourcompany.com&#039;,

@body=@mailbody,

@subject=@mailsubject;

END

The lack of a name for each of these is intentional, as is the encryption. The last thing I wanted was someone seeing these ans what they were doing, and if they had permissions to do so, disabling or dropping them to avoid having their nefarious behavior tracked. Even better would have been to put additional triggers in place to prevent the dropping of these no matter what, but I decided not to go there.

Note there is nothing there for the name for each of these – this is courtesy of the devious mind of Rob Volk. He might have too much time on his hands but this is pretty darn crafty. What you name these triggers is up to you but you have to MAKE NOTE OF WHAT THE NAME ARE!!! When I did these they were a combination of a few tabs and spaces – like “space space space tab tab” but with those actual characters. The result looks like this:

It should go without saying use this code at your own risk and always thoroughly vet and test anything before applying it to a production environment.

If this helps further cement my meanest DBA creds then I guess I am doing it right. Sometimes the elves developers can get out of hand and it is up to Santa the DBA to make sure they know they are being watched.