Differentiate between orphan user and one created WITHOUT LOGIN?

  • We have a process that identifies and removes orphan users. By "orphans" I mean users that were once tied to a login, but no longer are. Orphan users frequently arise when DB backups are restored on different servers, in different environments. Orphans are readily identified by trying to join users and logins.

    This works well enough, but a potential problem is that this process cannot tell the difference between an orphan user and one created using the "CREATE USER xxx WITHOUT LOGIN" syntax.

    Is there any way to differentiate between an orphan user and one that was created "WITHOUT LOGIN"?

    Or is there no difference between them...maybe the WITHOUT LOGIN syntax is simply a way to intentionally create an orphan user?

  • As far as I can tell, there isn't a way to differentiate between a real orphaned user and one purposely created without a login.

    Here's an interesting blog about the usefulness of the WITHOUT LOGIN option:

    http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

    Greg

  • Darn. Well at least 2 of us have come to same conclusion.

    When we start using "WITHOUT LOGIN" users we're going to have to abandon notion of programmatically identifying and removing orphan users.

    Thanks for replying.

  • Mike Good (7/17/2008)


    Darn. Well at least 2 of us have come to same conclusion.

    When we start using "WITHOUT LOGIN" users we're going to have to abandon notion of programmatically identifying and removing orphan users.

    Thanks for replying.

    I agree that there is no way to differentiate between a user that has become orphaned and one that was created without a log in deliberately, but if you know why you are using "without login" users, you may be able to check specifically for that.

    For instance, if you know that in your organization they are used only for "execute as" within procedures, you can programmatically check the procedure text for "execute as " and be fairly confident they became orphaned and are no longer needed if that does not exist.

    There may not be a general way to identify unneeded users, but you may be able to find a method that works specifically for your organization.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Mike Good (7/17/2008)


    Darn. Well at least 2 of us have come to same conclusion.

    When we start using "WITHOUT LOGIN" users we're going to have to abandon notion of programmatically identifying and removing orphan users.

    Thanks for replying.

    Good naming conventions/standards can help an awful lot here. And although I am not generally in the "heavy-handed DBA" camp, IMHO this is a perfect example of where the DBA should arbitrarily impose a standard. (And an "Exceptional" DBA would also know how to use her organization's Auditors to win any arguments over this.)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • The below stored proc will differentiate between orphan users and and not include those created with the "WITHOUT LOGIN" clause in 2005.

    USE master

    GO

    IF OBJECT_ID('rpt_security_detect_db_orphans') IS NOT NULL

    DROP PROCEDURE [dbo].[rpt_security_detect_db_orphans]

    GO

    CREATE PROCEDURE dbo.rpt_security_detect_db_orphans

    AS

    SET NOCOUNT ON

    /*

    NE 6/11/2007 - Detect orphan users in all dbs on a SQL instance; works in both SQL 2000 and 2005.

    EXEC rpt_security_detect_db_orphans

    8/27/2007 - NE - Modified to detect orphans better per SQL 2005, differentiating non-orphan db users

    created using the new SQL 2005 "WITHOUT LOGIN" clause. Based on changes to the "-- HANDLE REPORT --"

    section of the "sp_change_users_login" system stored procedure between SQL 2000 and SQL 2005. E.g.,

    "USE my_db; CREATE USER my_user WITHOUT LOGIN"

    1/8/2008 - NE - Added Windows users/groups in dbs that are orphans (corresponding login does

    not exist in SQL).

    */

    DECLARE

    @dbname varchar(200),

    @sql varchar(8000)

    DECLARE @temp table

    (

    dbname VARCHAR(500)

    )

    CREATE TABLE #dbOrphanUsers

    (

    DbName varchar(500),

    [User] varchar(500)

    )

    INSERT INTO @temp

    SELECT name

    FROM sysdatabases

    WHERE

    category IN ('0', '1','16')

    AND

    name NOT IN ('tempdb', 'model', 'pubs', 'northwind')

    AND

    name NOT LIKE 'adventurework%'

    AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

    ORDER BY name

    SELECT @dbname = MIN(dbname) FROM @temp

    WHILE @dbname IS NOT NULL

    BEGIN

    SET @sql =

    'INSERT INTO #dbOrphanUsers

    (DbName, [User])

    SELECT

    DbName = ''' + @dbname + ''',

    [User] =

    CASE

    WHEN b.[User] IS NULL THEN a.[User] + '' (not an orphan, but db user was created using the new SQL 2005 "WITHOUT LOGIN" clause)''

    ELSE a.[User]

    END

    FROM

    (

    SELECT DbName = ''' + @dbname + ''', u.name AS [User]

    FROM [' + @dbname + '].dbo.sysusers u

    WHERE

    issqluser = 1

    and (sid is not null

    and sid <> 0x0)

    -- and (len(sid) <= 16)

    and suser_sname(sid) is null

    ) AS a

    LEFT JOIN

    (

    SELECT DbName = ''' + @dbname + ''', u.name AS [User]

    FROM [' + @dbname + '].dbo.sysusers u

    WHERE

    issqluser = 1

    and (sid is not null

    and sid <> 0x0)

    and (len(sid) <= 16)

    and suser_sname(sid) is NULL

    ) AS b

    ON a.[User] = b.[User]

    UNION

    SELECT DbName = ''' + @dbname + ''', name

    FROM [' + @dbname + '].dbo.sysusers

    WHERE suser_sname(sid) is NULL AND isntname = 1

    ORDER BY DbName, [User]'

    EXEC(@sql)

    SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname

    END

    SELECT * FROM #dbOrphanUsers ORDER BY DbName, [User]

    DROP TABLE #dbOrphanUsers

    GO

  • Thank you StormNorm!!! I am impressed! And thank you for documenting your sources. I guess you ran into the same problem I did, but with more determination.

  • timothyawiseman and rbarryyoung: Thanks for replies. I'm not positive, but I think I like the idea of naming these users differently. Striving to be an excellent DBA....

  • Thanks StormNorm...it really helps...

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply