Removing user from SQL Server database

So, have you ever been in the need to drop a user from the SQL Server database, but you stuck with the following related error?

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

If you don’t have access to the SSMS to see which schema or objects the user owns, the following SQL should do the job.

USE [DATABASENAME]
GO
SELECT so.name OBJECT, su.name OWNER
FROM sys.schemas so
INNER JOIN sysusers su ON (so.principal_id = su.uid)
WHERE su.name = 'username'
GO
SELECT so.name OBJECT, su.name OWNER, so.xtype TP
FROM sys.sysobjects so
INNER JOIN sysusers su ON (so.uid = su.uid)
WHERE su.name = 'username'
GO

Once you have the owned objects/schemas, you can change them with the following SQL (schema example):

USE [DATABASENAME]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [newowner] /* new owner username */
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [newowner]
GO

Then you’re ready to drop the user DROP USER [username].

Bonus SQL: The following will list the schemas and their owner.

SELECT schema_name(schema_id) as SCHEMANAME,
user_name(s.principal_id) as USERNAME
FROM sys.schemas AS s

Stop installing MySQL 5.6!

From time to time, I get a customer request to configure/optimize a MySQL server, and usually, when the customer has already installed the MySQL Server, usually, it’s an “old” version. It’s ok if your software uses features that only work on some specific version of a Database, but keep an eye open for its end of life support. And that EOF is coming to MySQL 5.6. Below you can see a table with the MySQL versions and their ending date.

So, next time you need to configure a new server, keep an eye on the version you’re using.

Version Ending date
5.6 02/05/2021
5.7 10/21/2023
8.0 04/xx/2026

MySQL won’t start [ERROR] Found option without preceding group in config file

Hey folks,

have you ever received a call for a MySQL on windows that stopped working after someone did something at their my.cnf? Then you try to start the service by cmd and get the following error.

mysqld: [ERROR] Found option without preceding group in config file C:\ProgramData\MySQL\MySQL Server 8.0\my.ini at line 1.
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

Well, for some reason, the editor that was used (no idea which one was), threw some random byte at the beginning of the file. To solve that (on windows at least), open the file on Notepad++, go to Format > Convert to ANSI. Save the file and start again the service.

What was the weirdest thing that happened to you on a Windows Server?

MySQL Error1075 – Incorrect table definition; What’s happening?

Hey Folks,

A few months ago, I found an issue, where, for some reason, someone ignored the warnings and tried to restore a backup from a different version of MySQL (or even MariaDB, IDK). And as a result, half the database was running without Primary Keys. So when a system was trying to update their schema, we were getting errors (like the error bellow) trowed at our face.

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Ok, first things first, you would like to run an ALTER TABLE TABLE_NAME_HERE
ADD PRIMARY KEY (ID);
and see if it works.  The error was being thrown because the table key doesn’t have a single index on it… if you have problems with duplicated records on it, you can try the following script to solve the issue.

First, get the max id from the table, and then run the following: 

UPDATE TABLE_NAME_HERE JOIN (SELECT @sequence := MAX_ID_HERE ) r SET id=@sequence:=@sequence+1 where id= DUPLICATED_ID_HERE;

WARNING

Be aware that, if the rows that were duplicated, where referenced as FK on another table, you will get some headache (well, you already have problems…) !!

MySQL Error ‘Unknown or incorrect time zone’ at a replica

Have you ever tried to do a MySQL replication at a different timezone/SO and got the following error message at SHOW REPLICA STATUS? Well, there are two ways to solve this, Error ‘Unknown or incorrect time zone: ‘America/Cuiaba” on query. Default database: ‘glpi_tiab’. Query: ‘BEGIN’

If you are on Linux, you can run the following and solve the problem (after a restart of the service).

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

On Windows, I found it to be easier if you download the timezone instead of build/import your own. You can download from here https://dev.mysql.com/downloads/timezones.html.

use mysql;
source /path/to/file/timezone_posix.sql;

Well, that’s it for today. See ya.

Retrieve the SQL Server Version from a Backup File

Have you ever been in the need to retrieve the SQL Server version that was used on a backup file?

Well, if for some weird reason that happens to you, the following SQL can help you. It won’t restore the database, it’ll just retrieve some basic info about it.

With that, you’ll have the DatabaseVersion (Internal Number Version) where the backup was from. You can also grab some useful information like the Server Name, Creation Date, and more.

RESTORE HEADERONLY FROM DISK = N'b:\backup\data_backup.bak'

Below we have a table of  Versions x Internal Number.

Version Internal Number Version Compat. Level
SQL Server 2019 895 – 904 150
SQL Server 2017 868 – 869 140
SQL Server 2016 852 130
SQL Server 2014 782 120
SQL Server 2012 706 110

I hope it helps!

How to send Telegram messages from SQL Server

Hi folks,
You usually receive notifications from SQLServer at your e-mail, right? A nice way to receive updates from your SQL Server Schedule job, it’s sending them through telegram. To do so, you’ll have to configure one additional step and choose Operating System (CmdExec) as a type of command. And to call the Telegram API, we can use a PowerShell command like the following:

powershell "Invoke-RestMethod -Uri 'https://api.telegram.org/bot{API-KEY}/sendMessage?chat_id={CHAT-ID}&text={TEXT}'"

Just replace the following keys with the proper value (don’t forget to remove the curly brackets too).

API-KEY: Your bot API key
CHAT-ID: The ID of the chat where you’ll receive the message, could be a single user, or a chat group
TEXT: Your beloved message.

What else do you use to achieve this kind of alers? Slack? healthchecks.io? Tell us 😉

Microsoft Ignite – Certification Voucher

Hey folks,

Do you enjoy learning new things? Between September 22 and September 24, Microsoft will be hosting their yearly Microsoft Ignite event. Of course, it’ll be online this time and free of charge :).

This year, you can earn a certification voucher and that’s awesome. You’ll be able to choose one certification from a specific list. Check the list of available certifications and the Terms and Conditions accessing the following link: https://docs.microsoft.com/pt-br/learn/certifications/microsoft-ignite-cloud-skills-challenge-2020-free-certification-exam.

MySQL 8 requested authentication method unknown

Hey folks,
Be aware when you’re migrating from MySQL 5.7 to 8.0 on how you’re doing and what kind of applications have access to it. The version 8.0 introduced a new authentication plugin called caching_sha2_password and it’s the default auth plugin now.  Here is the list of compatible connectors to check if your app is ok with that or if you should call the dev team.

Example: If, for some reason, you get stuck with the following error (PHP sample), you have to do a few changes.

Connect Error: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

Edit your config file and set the option default-authentication-plugin with mysql_native_password value, restart the server and set the password again with the following command.

Config File:

[mysqld]
/... other configs .../
default-authentication-plugin=mysql_native_password

SQL:

ALTER USER 'adv'@'localhost' IDENTIFIED WITH mysql_native_password BY '1AB@8CD#E91F22!';

New users should be created the same way. Be aware that you have to do that just for users that the application will use. Regular users for DBAs and queries you don’t have to do that change.

So, look out before just doing a update on your server, or you could break your app 😉

MySQL InnoDB Buffer

Hi all,
Do you have a MySQL server running somewhere? I’ve seen many and many MySQL servers running with the default configuration, even the mysql_secure_installation command being ignored. Let’s talk about a tip to tune your server.

innodb_buffer_pool_chunk_size=134217728

This config, tells the server how many memory it can use, the default (using 8.0 and 5.7) its 128MB, that’s way less then it could be, in general. I usually set about 75% of the total ram of the server IF you just have the database on that server. With that, you have enough memory to accommodate OS processes and the MySQL. You can set this in your my.cfn file.

Be aware this is not a silver bullet, and if your server has a lot of ram, let’s say > 100GB, if you set at 75%, you still have about 25GB free, and that’s way more than the SO needs. So it’s all a matter of your server memory size.

Make sure you review this point on next time!