How to solve orphaned users in SQL & How to fix Orphan User in absense of login

Understanding and dealing with orphaned users in a SQL Server database

Refer:

http://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?

Solution

When a database is moved from one server to another server the login ids stored in the master database do not align with the login ids stored in each user database. As mentioned above some of the errors you may see after attaching or restoring a database include:

Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s

or

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

Without proper understanding and appropriate planning you may face this problem. You could delete and recreate the user, but you will loose all of the configured permissions. So a proper linking mechanism is required, so that permissions are retained.

Some more possible error messages that you may see include

Before getting to the solution for this problem, it would be better to have a glance at the backend issue. SQL Server logins, which are stored in the master database are mapped against individual databases. SQL Server logins access individual databases using a database user that is mapped to the appropriate SQL Server login. There are two exceptions, the guest account and Microsoft Windows group memberships. The SQL Server 2005 logins on a server instance are visible in the sys.server_principals system catalog view and the sys.syslogins view. For SQL Server 2000 you may get SQL Server login information in sysxlogins table.

On the other hand the mapping information to a database user is stored within the database in the system tablesysusers. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization purposes in the database.

So we can say that every time we create a SQL Server login, it will be seen in the sys.server_principals system catalog view or sys.syslogins views on SQL Server 2005 or in the sysxlogins table for SQL Server 2000. The entries inthe sysusers table of a database are linked to SQL Server logins shown in the above picture. This link is created through a column named SID.

If we move our database to another SQL Server instance through any process, the new server might or might not have the same logins and the SIDs of these logins would probably be different from the SIDs of these logins in the original server. What this means is that, the sysusers table in the moved database has SIDs that are not matched with the login info in the master database on this new server. Therefore we get orphaned users.

As an example, I have created and configured four users with permissions in the AdventureWorks database. The users are TestUser1, TestUser2, TestUser3 and TestUser4. When I restored the backup of this database onto another SQL Server 2005 instance, although the users were present in the AdventureWorks database and the logins existed on the new server, but none of these logins had access to the newly restored database.

So keeping this scenario in mind, let us run some queries to see the difference between the SQL Server login SIDs (if SQL Server login is present) and database user SID for TestUser3.

--Script to view difference in SID

USE MASTER
GO 
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO

USE AdventureWorks
GO 
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

The result below shows the SID of the SQL Server login and that of the user database id differ and this is what causes the problem.

Now that we have a better understanding of the problem, it is time to get to some useful commands for analysis and a solution.

I have restored the AdventureWorks database from one instance to another with the four above users. Now to analyze how many orphaned users there are in my restored database, I will run the following T-SQL command which produces a listing of all the orphaned users and in our case all four users are orphaned.

--Command to generate list of orphaned users

USE adventureWorks
GO

sp_change_users_login @Action='Report'
GO

Now that we have the list of the orphaned users we can begin to fix the problem. To overcome this problem, you need to link the SIDs of the users (from sysusers) to the SIDs of the valid logins in the master database. The following command remaps the server login account specified by TestUser1 with the database user specified by TestUser1.

--Command to map an orphaned user

USE AdventureWorks
GO

sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'
GO

Or if you are sure that SQL Server login name is the same as the orphaned user of database being mapped, then you may use a shorter command such as this for TestUser2..

--Command to map an orphaned user

EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO

Both commands will map the users to the logins and they will be not orphaned any longer.

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

--Command to map an orphaned user to a login that is not present but will be created

EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'
GO

Summarizing the T-SQL Used

In the above process, the stored procedure sp_change_users_login is used. The variable [ @Action ] specifies the exact use of this stored procedure. It accepts a parameter as varchar(10) and can have one of the following values:

• If parameter is Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present.
• If parameter is Report, it lists the orphaned users and their security identifiers (SID).
• If parameter is Update_One, it links the specified database user to an existing SQL Server login.

Some considerations

• sp_change_users_login requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.
• When the orphaned user is mapped, the SID in the master database is allotted to orphaned user, so every time a DB is attached or restored the SID will differ between SQL Server login and database user.
• If you have different Server login names mapped to a database user then do not use the command with Auto_Fixfor linking
• A user may become orphaned also if the corresponding SQL Server login is dropped
• Although obvious, but it is good to mention, after re-linking the password of SQL Server login can be used by the database user.

How to fix Orphan User in absense of login

Error
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131
Terminating this procedure. The Login name ‘DJ’ is absent or invalid
What is Orphaned User?
An Orphaned User in SQL Server is a database user for which a valid SQL Server Login is not available or it is wrongly defined with the different SID in the SQL Server instance, thereby not allowing the user to get connect to the database to perform activities.
Below scenarios are mostly responsible for Orphan Users
1. A SQL Server Login was accidentally dropped
2. A database is restored with a copy of database from another SQL Server Instance
3. SID of the login is different in sys.server_principals and sys.sysusers
Steps to re-produce issue
Step 1: Creating database SQLDBPool

USE [master]
GO
CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N’sqldbpool’, FILENAME = N’C:\sqldbpool.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’sqldbpool_log’, FILENAME = N’C:\sqldbpool_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Step 2:
In this example I am creating user DJ using below script. You can also create user from GUI as well
Using GUI

Right Click On Security Node and Select New Login

Using Script

USE [master]
GO
CREATE LOGIN [DJ] WITH PASSWORD=N'pune@123',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

Step 3: I am assigning Data Reader and writer permission to Login DJ to database SQLDBPool

USE [sqldbpool]
GO
CREATE USER [DJ] FOR LOGIN [DJ]
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_datawriter', N'DJ'
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_denydatareader', N'DJ'
GO

Step 4: Dropping the login DJ and it will make the user DJ as orphan in SQLDBPool database

USE [master]
GO
DROP LOGIN [DJ]
GO

Steps to fix the issue
Step 1: You can use SP_CHANGE_USERS_LOGIN stored procedure to fix the Orphan user issue, for that you need the Database Owner permission on that database.
Syntax
sp_change_users_login [ @Action= ] 'action'
[ , [ @UserNamePattern= ] 'user' ]
[ , [ @LoginName= ] 'login' ]
[ , [ @Password= ] 'password' ]
[;]
Examples

EXEC sp_change_users_login 'Report' --Reports orphaned user
EXEC sp_change_users_login 'Auto_Fix', 'user' -- auto fix when SID mis-match issue
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

The stored procedure SP_CHANGE_USERS_LOGIN accepts arguments AUTO_FIX, REPORT or UPDATE_ONE as @Action parameter.
AUTO_FIX: If this value is used for @Action parameter, it will create a SQL Server Login if it was not present earlier and will synchronize the SQL Server Login with that of the Database User.We have to also provide the password here.
REPORT: It will display the list of all the Orphaned Users along with the SID (Security Identifiers) value within the current database which are not linked to a SQL Server Login.
UPDATE_ONE:will synchronize the specified database user with an existing SQL Server Login.
Step 2: As first step I am checking orphan user using Report parameter, it will show me the SID and orphan user name.

List of Orphaned User

Step 3: In this scenario we have dropped the SQL Login so it is required to create the login. We can create the login using below query specifying password and mapped it to orphan user

EXEC sp_change_users_login 'Auto_Fix', 'DJ', NULL, 'pune@123'

Step 4: Executing again the report query to list out orphan users.
EXEC sp_change_users_login 'Report'
Use below link to list out and fix the Orphan User issue for all the databases.
http://sqldbpool.com/2010/03/27/script-to-fix-and-list-out-orphan-users-on-all-the-databases-on-server/