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.
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.
Please +1 and share the post if you like it. And provide your valuable feedback as comments.
Comments
Post a Comment