SQL Server - Curious case of SQL orphaned users

Hello everyone and welcome back to your friendly SQL blog. While working on a recent project, which was to get SQL login related information, I came across an interesting ambiguity regarding orphaned users in SQL Server that a database user could be orphaned and still may not come up in your orphaned users report. Sounds interesting, yes it is. 
Remediation of this scenario unearthed a flaw/miss of our old and reliable inbuilt SQL Server stored procedure i.e. “sp_change_users_login”, which we generally use to verify broken or orphaned Database SQL users. Such interesting facts and useful piece of information are meant to be shared and hence came up this blog of today.

Scenario:

Now let me put more details regarding this particular scenario. Generally, a user is called orphaned when you’ve a database user present on the database that has no corresponding SQL login present on the server level. This relationship between a database user and SQL login is established using SIDs of both the secure object. A regular non-orphaned user will have a corresponding login present on the server level with same SID (there name doesn’t need to be same) as that of database user.

Now what was happening here is that, there was a login which was present on server level and was mapped nearly to every database on the server. I was using the SID matching logic to get report of orphaned users and on few of the databases we’re getting a red flag that this ID is orphaned. As this report would be shared with end users as well as we also they would be dropping these orphan users hence we were validating the data before this goes to production.

As usual for validation we used "sp_change_users_login ‘report’ " command to get report of orphan users on databases. However, when we executed the command on flagged database the ID didn’t get listed as orphan. Obviously, when you get a contradictory result for a reliable logic you ought to dig deeper to get to the root cause and this is exactly what I did.

Issue:

To get to the root cause of this issue I tried to reproduce the scenario on my local system. To recreate this issue all you’ve to do is to create a database user on any database without login as can be seen below.

Now as you can clearly see above this user is not mapped to any login as we have explicitly created it without any mapping, however when you run the orphaned user report you’ll still zero rows returned.

Then I went into the text of “sp_change_user_login” SP to see why this user is not getting reported. Below is the snippet from the SP for the report section. There is a particular line (which I’ve also highlighted) is what causing this user to not get reported.

-- HANDLE REPORT -- 

    if @Action = 'REPORT' 

    begin 

 

        -- CHECK PERMISSIONS -- 

        if not is_member('db_owner') = 1 

        begin 

  raiserror(15247,-1,-1) 

             return (1) 

        end 

 

        -- VALIDATE PARAMS -- 

        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null or @Password IS NOT Null 

        begin 

            raiserror(15600,-1,-1,'sys.sp_change_users_login') 

            return (1) 

        end 

 

        -- GENERATE REPORT -- 

        select UserName = name, UserSID = sid from sysusers 

            where issqluser = 1  

            and   (sid is not null and sid <> 0x0) 

            and   (len(sid) <= 16) 

            and   suser_sname(sid) is null 

            order by name 

        return (0) 

    end 

 

   -- ERROR IF IN USER TRANSACTION -- 

    if @@trancount > 0 

    begin 

        raiserror(15289,-1,-1) 

        return (1) 

    end 

There are four cases which are tested and the third (highlighted) clause was the main reason for the miss. If you see in the below screen-shot even though it is an orphaned user the length of the SID is longer than expected 16 and hence it will not show as orphaned by “sp_change_users_login”.

So, this is it, our curious case of hidden orphaned SQL users. Working on this scenario also highlights the importance of testing and validation of our processes and results. No matter how much you’ve tested on your test system, PROD systems can still throw surprises, which will make you go back to your code and to look for such hidden flaws of system and functions you’re already using.

Hope this will help you in case you’re developing something like that or wondering why a user is not having access to database when he/she is have a mapped login. They could be orphaned and we may have not even noticed it. 

Please +1 and share the post if you like it. And provide your valuable feedback as comments.