Fix Orphaned Users in SQL Server - Schema Error: 15183

What is Microsoft SQL Server Error 15183?

Message:15183, Level 16, State 1, Line 1

The database principal owns a schema in the database, that cannot be dropped. SQL Error 15183 comes when moving of the database from one server to another server or when we want to change it from one domain to another domain.

Reason of Orphaned Users Error 15183

The restoring process of old master database can also raise this problem. As we all know that the every SQL Server logins data are stored in the system table “sysxlogins” which resides in the master database. When you create a new login, then an entry related to the newly created logins added to the “sysxlogins” table. So there is a corresponding row present in the sysxlogins table and each logins also have an associated Security Identifier.

The problem comes when we move the databases from one server to another and new server might not have the proper logins. The security identifiers are also quite different from the security identifier of these logins on the old server. And the newly moved database SIDs are not present in the “sysxlogins” table on this new server. That’s why the SQL Server unable to map the users in this database to any of the logins. At the end result comes the user is an orphaned user.

How to Detect Orphaned Users in Schema

We can detect the orphaned user in the databases using this T-SQL query.

USE ; GO; sp_change_users_login @Action='Report'; GO.

This query shows the orphaned users with their security identifiers which are not linked to any SQL Server logins. sp_change_users_login can not be used with the windows account.

Troubleshooting Microsoft SQL Server Error 15183

To fix the error you need to change the schema owner. You can change the schema owner using Transact-SQL statements or SQL Server Management Studio. First of all we will discuss the changing the permission of the schema using T-SQL.

Troubleshoot the orphaned users follow the given steps:

1. Relink the server logins account.

USE ;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='',
@LoginName='';
GO

2. After running this code you need to change the password of the login account by using sp_password stored procedure, like this:

USE master
GO
sp_password @old=NULL, @new='password', @loginame='';
GO

Altering the Database Schema using SQL Server Management Studio

Given steps to change the schema owner of the orphaned user in the database.

1. Expand your database -> Security -> Schema.
2. Right click on the schema, that you want to change.
3. Go through the – Properties -> General -> Schema Owner.
4. Run this query to change the owner’s permission of the database.

USE schema_name;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘schema_name');

Conclusion

In this article we have discussed about Microsoft SQL Server Error 15183 and how can we find the orphaned users having the owner of the schema in the databases. This article also describes how to troubleshoot of orphaned users in the database using T-SQL query and SSMS.