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

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 😉

Teradata ODBC for Unix OS

To connection between unix machine with teradata environment is needed to install ODBC Driver and some others components (dependencies). All Items can be found in Teradata Downloads site: https://downloads.teradata.com/

Teradata OBDC Driver has 3 dependecies:

1. Shared common components for Internationalization (TDICU)
2. Teradata GSS client package (TeraGSS)
3. Teradata Tools and Utilities Base (TTU)

After download all items, run as root: rpm -ivh “component_name” –nodeps

Eg: rpm -ivh teragss1510-15.10.02.06-1.noarch.rpm --nodeps

Use “–nodeps” only to not need to install in order

More“Teradata ODBC for Unix OS”

Converting Between SQLServer, Oracle, PostgreSQL, MySQL, Sybase and others…

Hi all!
I was asked to make a conversion from T-SQL (MSSQL) Procedure to PL/PGSQL. Regarding how boring is this task, the follow link helped me:
http://www.sqlines.com/online

I highly recommend it. The site has a commercial solution to convert all database, but some code can be converted online for free. 🙂
The conversion not fixed at all, but make a good part of the work… And all help is helpful…

More“Converting Between SQLServer, Oracle, PostgreSQL, MySQL, Sybase and others…”

GoldenGate: Replicate data from SQLServer to TERADATA – Part 2

This steps should still be performed in SQLserver Host:

The pump process configuration is very simple, its only function is to transport the trail files to destination.

ADD extract P_MSQL, exttrailsource ./dirdat/tr

C:\goldengate> edit param P_MSQL

extract P_MSQL
SOURCEDB db0sql1, USERID ggate, PASSWORD ??????
CACHEMGR CACHESIZE 2GB
rmthost teradata1.net, mgrport 8809
rmttrail ./dirdat/td

--TABLE MAP
TABLE dbo.DLOG_ERRORS;
TABLE dbo.SAC_DATA;
TABLE dbo.SAC_LIST;
TABLE dbo.SAC_TITLE;

Still in the SQLserver Host, is need to create a definition file, wich will be used in gg-teradata.
First, create a “tables.def” file that should contain a dblogin and tables that will be replicated.

defsfile tables_sqlserver.sql purge 
SOURCEDB db0sql1, 
USERID ggate, PASSWORD ?????? 
TABLE dbo.DLOG_ERRORS; 
TABLE dbo.SAC_DATA; 
TABLE dbo.SAC_LIST; 
TABLE dbo.SAC_TITLE;

More“GoldenGate: Replicate data from SQLServer to TERADATA – Part 2”

GoldenGate: Replicate data from SQLServer to TERADATA – Part 1

Since we are arriving at the end of the year, I have taken the mission to replicate data between SQL server and TERADATA. The worst part in this task, is to install and configure a Goldengante in a Windows environment.

Believe, it is not possible to do a Unix installation of goldengate to collect data from SQLserver, goldengate binary needs to be installed on Windows SQLserver host.

After installing the GG binaries, it is good practice to add the MGR as a Windows service:

C:\goldengate> install addevents addservice manualstart

Oracle GoldenGate messages installed successfully.
Service 'GGSMGR' created.

Install program terminated normally.

In order for GG to access the sql database, you need to create a data source (ODBC), and configure a new system DSN (here is db0sql1), and select SQL Server as the database driver.

More“GoldenGate: Replicate data from SQLServer to TERADATA – Part 1”