Post reply

How to determine orphaned users without using sp_change_users_login?

  • July 9, 2009 at 5:38 pm

    #202228

    In BOL for SQL 2008, under sp_change_users_login, we are supposed to stop using sp_change_users_login and instead use ALTER USER.

    One of the parameters for sp_change_users_login is "Report", which:

    Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login. user, login, and password must be NULL or not specified.

    My question is this: what are we supposed to use to determine orphaned users?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 4, 2009 at 3:36 pm

    #1049630

    EXEC sp_validatelogins

  • September 4, 2009 at 3:55 pm

    #1049636

    This will pick up users that were created using the WITHOUT LOGIN clause, but I don't know of any easy way to differentiate them from orphaned users. And likely there are few cases where you will have those types of users.

    SELECT dp.[name], dp.sid

    FROM sys.database_principals dp

    LEFT JOIN sys.server_principals sp

    ON dp.sid = sp.sid

    WHERE sp.[name] IS NULL

    AND dp.is_fixed_role = 0

    AND dp.name NOT IN ('public', 'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');

    K. Brian Kelley
    @kbriankelley

Viewing 3 posts - 1 through 2 (of 2 total)

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