Category: Solutions

This error is caused Intermittently by the Log reader agent in the transnational replication. When you observe the error, the database principal ‘dbo’ associated to the login mapped to the ‘dbo’ user cannot be impersonated. The database owner or login associated to ‘dbo’ user are not ‘sa’ but other windows authentication/SQL authentication login.

Solution:

1. Stop the Log reader agent
2. Change the ownership of the database using the below query:

ALTER AUTHORIZATION ON DATABASE::Databasename TO sa;
GO

3. Restart the Log Reader Agent.

Conclusion:

It is always advisable to map the ‘dbo’ user of the login to ‘sa’ to avoid the intermittent issues with Log reader agent in Transnational Replication.

Everything seems to be normal until someone yells at a DBA asking why they couldn’t give database role permissions to a login due to this error.

This error occurs when particular login in question is associated to ‘dbo’ user in the database. In other words, this particular login is the ‘db_owner’ of the database which is causing the issue.

This error occurs while we try to give any database role permissions to the login associated with ‘dbo’ user

I recreated the error by creating a new database name ‘Test’, create a new login with name ‘TestUser’ and giving the authorization to the newly created ‘testuser’.

CREATE DATABASE TEST
GO
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'@eE$9@', DEFAULT_DATABASE=[master]                
GO
ALTER AUTHORIZATION ON DATABASE::[Test] TO [TestUser]
GO

In the Login ‘TestUser’ properties, database mapping tab, you should see the login associated to user ‘dbo’

I then tried to add a database role ‘db_datareader’ to the database Test which throws an error

Solution:

Change the database owner to ‘sa’, then create a user associated to the login in the Database and then add the login to database roles with the new created user.

USE [Test]
GO
-- Change the database owner to 'sa' account
ALTER AUTHORIZATION ON DATABASE::[Test] TO [sa]
GO
USE [Test]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestUser]
GO

Conclusion:

These kind of errors seems to be tricky in the beginning, but once we understand the reason behind it, its pretty easy to solve. These kind of errors are caused by couple of reasons including database migration from lower environments of sql server. When the database is migrated to latest versions, the user ‘dbo’ user was not associated with ‘sa’ account. The other reason is when the authorization of the database is changed to specific login, ‘dbo’ user is associated to the login. It is always important to check which login is associated with the ‘dbo’ user in the database. You can check that using the below query:

--check which login is associated with 'dbo' user
select ab.name as DatabaseUser, cd.name as SrvrLogin, ab.type_desc, ab.owning_principal_id, ab.authentication_type_desc
	From sys.database_principals ab
		inner join sys.server_principals cd
			on ab.sid = cd.sid
where ab.name = 'dbo';

The result of the query looks like this:

Hope this helps!

While we upgrade the sql server, we do upgrade the DTS/SSIS packages to the latest versions compatible with the upgraded sql server version. SSIS packages for sql server 2008/2008 R2 are build by Business Intelligence Development Studio (BIDS) and sql server 2012 or higher are build by using SQL server Data tools.

In some situations, Developers build SSIS packages using latest version of Data tools thinking it can be compatible with lower versions of sql server. I recently faced this situation where a developer build a package in 2012 version of data tools and tried to run the package in sql 2008 through sql agent job.

Changing the package version seems to be an easy fix, but usually its not!

Here’s why?

Once you change the package version by right clicking the dtsx package>open in notepad and change the version from 6 (sql 2012 version number) to 3 (sql 2008 version number), the package will be able to run on sql 2008 via sql agent job. But what about the execution results? Are they executing properly? Not always.

To test this scenario, I created a SSIS package using sql 2008 BIDS with name package.dtsx. This package has a execute sql task which executes a simple error raising script to see how these versions handle errors.

Below is the piece of code I use in the execute sql task for both versions of packages

Raiserror(‘Something went wrong’,16,1)

Note: For both the SSIS packages created, in the Execute SQL task properties> set both FailPackageOnFailure and FailParentOnFailure to ‘True’. This will make sure the package fails if the Execute SQL task fails.

Once I created the package, I tried to execute this ‘package.dtsx’ ( created by using sql 2008 BIDS) on sql 2008/2008 R2 using sql agent job. Ideally, it should throw an error.

Executed the job and as expected, job failed. This is an expected behavior.

I Created the same package in Data tools 2012 with name ‘package_2012.dtsx’. Open the package in notepad and Change the package version 6 to version 3 and save the file.

Now run this ‘package_2012.dtsx’ using sql server agent job

Execute the job and check the results, it get executed successfully. In this scenario. this is a wrong behavior. As Execute sql task has raiseerror code in it, package should throw an error, but it didn’t.

Conclusion: By just changing the Package version from higher to lower version numbers may allow lower versions of sql server execute the package through sql agent job but It doesn’t mean that the package is executed efficiently. In this scenario, package should fail by throwing an error but it doesn’t fail and executed as success. It is always better to create the SSIS packages using BIDS or Data tools based on the sql server version on which the package should run.