Where have all the Orphans gone?

Categories: Corner, News, Professional, SSC, SSSOLV
Comments: 2 Comments
Published on: October 3, 2012

Do your database users appear to be having an out of body experience?  Have they lost all ties to their login?

Does it feel like maybe what this baby rhino might be experiencing – just trying to find their place in your database?

Well, have we got a little snippet for you then.  This cure-all elixir … er script may be just what you need for those ghostly ghastly things called orphaned users.

Everybody has heard of them.  Everybody seems to want a quick fix for them.  But before you can fix your orphanage / database of these orphans – you have to know that they are there.


Cute Rhino

Well, not really – looks more like a hairless rat at this age.  But that is ok – I like rhinos of all ages.  But that is really beside the point.  I also like quick little scripts that will help me get a better picture of any databases that I may have inherited or that I must work on (which I had not previously worked on).

This script is naturally another one of those tools for the admin toolbox.  It is cursor based – but that is ok.  I might update it later to use a set based method and that new foreachdb that I have blogged about previously.

The reason for the looping is to run this quickly against all databases on an instance and immediately know where the orphans might be hiding.  This script is merely to report – it does not fix the orphans.  That is for you to work through.  Also of interest is that this script is not designed to work on SQL 2000 (it’s getting kinda up there in age anyway).

So here it is…another tool for the toolbox to help you become a better more efficient DBA.

[codesyntax lang=”tsql”]


2 Comments - Leave a comment
  1. Nico van der Walt says:

    Hi Jason,

    Thanks for your very handy orphan script (http://jasonbrimhall.info/2012/10/03/where-have-all-the-orphans-gone/#codesyntax_1).

    This is running fine on our SQL2008 servers, however get the following error on our SQL2005 server:

    Msg 139, Level 15, State 1, Line 0
    Cannot assign a default value to a local variable.
    Msg 137, Level 15, State 1, Line 29
    Must declare the scalar variable “@SQL”.

    For my own growth, I have tried playing around with the code to see if I could overcome the error, but it persists. Could it be a collation issue or could the code be modified?

    Many thanks for a very useful site.


    • Jason Brimhall says:

      The variable is declared and set in the same command. This syntax is new in SQL 2008.

      Just make a change like this

      From what it is currently
      @DBName SYSNAME,
      @SQL NVARCHAR(MAX) = N”;

      To something like this
      @DBName SYSNAME,
      Set @SQL = N”;

Leave a comment

Your email address will not be published. Required fields are marked *

October 2012
« Sep   Nov »


  • @SQLSoldier: @mvelic Yes, but then I pointed out that they were using nolock and SSIS isn't. The matching records were not committed. #sqlhelp #TrueStory
  • @mvelic: It's just maddening because this lookup has *always* worked in the past. It's just now deciding to not recognize matches. #sqlhelp
  • @mvelic: Has anyone just seen an SSIS Lookup fail to make matches? You know the matches exist, but it doesn't connect them and it fails? #sqlhelp
  • @banerjeeamit: @MattPgh No. Current processing report is not visible. This is visible in RunningJobs table but not the stats breakdown. #sqlhelp
  • @forhakim: #sqlhelp in Visual Studio SSDT is there a way to make it NOT show table designer, only the script, when I edit a table?
  • @MattPgh: @banerjeeamit Will the current report show up in ExecutionLog? whatever processing is happening did not finish yet. #sqlhelp
  • @banerjeeamit: @MattPgh Look at the time processing and rendering in the logging table: http://t.co/1n2ZX5Ywwi #sqlhelp
  • @banerjeeamit: @MattPgh Also, CPU time can be consumed due to rpt processing. This is available thru the ExecutionLogStorage table #sqlhelp
  • @banerjeeamit: @MattPgh Using XEvents or profiler u can see which stmt r CPU intensive? This wud gv u the cpu time consumed by the DB queries. #sqlhelp
  • @MattPgh: Is there a way to tell exactly what SSRS service is doing when it has CPU pegged to 100%? like a "what running" query in sql. #sqlhelp

Welcome , today is Saturday, October 10, 2015