What To Expect After Database Refresh?

Recently, I completed a database refresh project. The databases from production SQL Instance were refreshed on to QA SQL Instance. The QA database is now almost in sync with our Production Instance. QA and developers are loving the fresh data. The project completed with few hiccups even though they were minor ones and could have been avoided easily. Lesson that I learnt form this project is  worth sharing .

After the completion of each project, I would like to test and verify to see if it is working as expected. Database refresh worked for me. The db objects counts are same on both the Instances. The record counts for few sample tables on both instances are also same.  I then  asked the developer to verify the db refreshes. The developer pinged me back as soon as they got my email on testing. The application they were testing couldnot connect to the database therefore they can’t move forward with their testing.

What is going on here? I can log into all user databases and access to their database objects. May be the privilege to the application user got messed up while moving from one server to another. I tried logged in as an application user using SSMS, the database won’t let the app user logged in. I checked to see if  the user login  exist in our QA server. The user login exists on sys.server_principle and in sys.systemlogins view. The user also have read and write access to all user databases.  It certainly does not look like a user privilege issue, what do you think is the issue?

I changed the default database for application user to master db from user database to see if app user can get access to master db.

GO
ALTER LOGIN [test_user] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

After changing the default database to master, the user can log in the QA SQL Instance but unable to access any user databases even though the user have correct roles and privileges. While I was researching for database restore project, someone talked about the user might be orphaned. You are required to check and fix orphan SQL users immediately after refresh or moving to a new server. I admit that I  totally forget to check orphan user. The app user was orphaned because of the refresh. The next few paragraph will talk about how to check and fix the orphan user.

How to check orphan user?
Pick a database that app user don't have access to and  run the below query and see if the "login_type " is NULL or "login_name" is Orphaned!

SELECT dp.NAME                       [user_name], 
       dp.type_desc                  [user_type], 
       Isnull(sp.NAME, 'Orhphaned!') [login_name], 
       sp.type_desc                  [login_type] 
FROM   sys.database_principals dp 
       LEFT JOIN sys.server_principals sp 
              ON ( dp.sid = sp.sid ) 
WHERE  dp.type IN ( 'S', 'U', 'G' ) 
       AND dp.principal_id > 4 
ORDER  BY sp.NAME ;

Output

Orphan User

The query confirmed the user is orphan. When the user is orphaned, the user don't have access to user database, you will need to fix it before it becomes a valid user. Running a stored procedures is one way to fix it fast.

EXEC sp_change_users_login 'Auto_fix','test_user';

Output:

The row for user 'AdventureWorks' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

The app user was able to access the database and everyone was happy, I ran the stored procedures on all the user databases. To my surprise, I found few NT accounts were orphaned as well but the stored procedures did not fix the issue. Why didn't it fix the problem? The NT users were not in a Master database therefore, I had to add those users into a master database and then the procedure fixed the database access.

USE [master]
GO
/****** Object:  Login [DBAREPUBLIC-NT\DBA] cript Date: 02/05/2015 12:19:51 PM ******/

CREATE LOGIN [DBAREPUBLIC-NT\DBA] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

We fixed the orphaned user with SQL Server but never discussed the cause. What is an orphan user and what cause it? I believe understanding the root cause of any problem is more important before seeking a solution.The server log in ID aka SID in master database does not match the SID of each user databases when a database is moved or refreshed from different server.

When a database is moved from one server to another server log in ids stored in the master database do not align with the log in IDs stored in each user database. When there is mismatch in SID, the user can't access the database and becomes orphan.

Microsoft experts states:

1. A database user for which the corresponding SQL Server log in is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.
2.  A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server.
3. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Always, it is a brilliant idea to research the impact, testing it in development/testing environment and making a backup before doing any type of changes to a database. People say, "you learn from your mistakes" but here you all learnt from my mistake not yours!

Source:
https://msdn.microsoft.com/en-us/library/ms175475.aspx