x
login about faq Site discussion (meta-askssc)

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 '09 at 04:48 PM in Default

Kev Riley gravatar image

Kev Riley ♦♦
46.1k 39 43 69

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 '09 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 '09 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 '09 at 05:03 PM

Tom Staab gravatar image

Tom Staab
5.8k 5 8 10

(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

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1612
x916
x5

asked: Nov 19 '09 at 04:48 PM

Seen: 1164 times

Last Updated: Oct 25 '10 at 02:45 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.