Two questions that sometime come up in discussion forums as well as at client sites are related to Orphaned Sessions (Oracle, SQL Server, DB2 LUW) and Orphaned Users (SQL Server). In this post, we are going to talk about the later one. Before we start talking about orphaned users and how to detect and resolve the issue, it is important to understand how the Authentication/Authorization process works in SQL Server. We are not going to cover the user-schema separation in this post – you can read more on that here.
In order to log into SQL Server, you need to be using either Windows Authentication or SQL Server Authentication. These logins need to be registered with a particular SQL Server instance in order to allow that particular login to be able to connect to an instance. The first step in the process of connecting is the authentication of this login (principal) to see whether it can connect to the given instance of SQL Server or not. You can see these login (principal) accounts using either sys.syslogins view or the sys.server_principals view. Once the authentication has been established, the next step is to see whether that particular login has a mapping to a user within the SQL Server database i.e. there needs to be a mapping between the login and a user within a database on the SQL Server instance that you are connecting to.
Please do note that if you have enabled the “guest” account within a database, then all logins do not need to have a valid user mapping in the database and can enter as a guest user. Also, if you have a Windows login and that login has a membership in a Windows group (which is mapped to a user in the database), that login can also access the database without having a direct mapping to the user.
So, with that information in mind, what is an orphaned user? An orphaned user is a user account in the database for which the corresponding login on the server instance is undefined or incorrectly defined i.e. it has no mapping to any login (principal) on the instance. And when can this happen, you ask? It can happen if a login is dropped from an instance without re-mapping the user in the database first. It can also happen if you have taken a backup from a source instance and restored it to a destination instance (same is true for the attach/detach as well) – this ties into our post from 2 days ago on how to copy databases from one instance to another.
Now that we have understood what orphaned users are and how we can run into this situation, let’s see how we can detect such orphaned user accounts in a database. You can simply run this SQL:
Simply replace DECIPHER_TEST with your database name on the instance. And you can also use this system stored procedure to also re-map the orphaned users:
sp_change_users_login @Action=’update_one’, @UserNamePattern=’orph_user’, @LoginName=’sqladmin’