Identifying orphaned users

As a precursor to this question, and with the announced deprecation of sp_change_users_login, how are we to identify orphaned users, in the same way that we used to use

sp_change_users_login 'report'
more ▼

asked Nov 19, 2009 at 04:48 PM in Default

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Whilst I like Tom's answer (and I have accepted it), I'm still surprised that there isn't an 'official' way of doing this...

Nov 30, 2009 at 07:38 AM Kev Riley ♦♦

I agree, Kev. As a software developer, I learned years ago that it is usually a bad idea to reduce functionality in new releases. While techinically the functionality is still there in this case, it is certainly not as easy to use.

Nov 30, 2009 at 12:39 PM Tom Staab ♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I ran sp_helptext to get the code for the report section of sp_change_users_login:

select UserName = name, UserSID = sid from sysusers
    where issqluser = 1 
    and   (sid is not null and sid <> 0x0)
    and   (len(sid) <= 16)
    and   suser_sname(sid) is null
    order by name

I then converted that to SQL Server 2005/2008 code:

SELECT UserName = dp.name, UserSID = dp.sid
FROM sys.database_principals dp
WHERE dp.type = 'S'
    AND (dp.sid is not null AND dp.sid <> 0x0)
    AND (LEN(dp.sid) <= 16)
    AND SUSER_SNAME(dp.sid) is null
ORDER BY dp.name
more ▼

answered Nov 19, 2009 at 05:03 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 19, 2009 at 04:48 PM

Seen: 1991 times

Last Updated: Oct 25, 2010 at 02:45 AM

Copyright 2018 Redgate Software. Privacy Policy