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 »


  • @RP_RATNA: VM hitting 100 % cpu always SQL load is normal but still CPU 100 % how to trouble shoot checked all top performing queries #sqlhelp
  • @SQLSoldier: @grocep Yes you can. You probably won't notice either way unless you're under heavy external memory pressure. #sqlhelp
  • @grocep: Can i set ‘min server memory’ to 1/2 total memory on a SQL VM with ballooning turned off? Is it good or bad practice? #sqlhelp
  • @intentShared: #sqlhelp in 2012 you can add an AG listener and immediately start listening, in 2008r2 anyway to pick up a new IP without restarting svc?
  • @AdamMachanic: @thinbluedba have you looked at application roles? #sqlhelp
  • @Kos1mo: @AngryPets Yes, thank you for putting it in context. #sqlhelp @SQLSoldier
  • @AngryPets: @Kos1mo @SQLSoldier #sqlhelp It was replaced, effectively, by Linq to SQL then the EF. (Put another way: they're why there are no updates.)
  • @thinbluedba: #sqlhelp I'm looking for something like a proxy that would hold/prevent certain queries from running within SSMS, does Hexatier do that?
  • @Kos1mo: @SQLSoldier thanks that helps. Am looking at code that looks like it came from that sample. Binging for rants/wisdom about it. :) #sqlhelp
  • @SQLSoldier: @Kos1mo Last update I can see was June 2005. I don't think it is used any more nor replaced by something. #sqlhelp

Welcome , today is Friday, May 6, 2016