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://www.jasonbrimhall.org//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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

October 2012
« Sep   Nov »

Welcome , today is Friday, July 20, 2018