x

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 ♦♦
50.7k 43 49 76

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 6 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

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

Topics:

x1816
x977
x5

asked: Nov 19 '09 at 04:48 PM

Seen: 1527 times

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