Skip to content

Fix orphaned users in SQL Server 2005 – not

by on August 29, 2011

A quick one, again (haven’t been blogging for a while, I have like a ton of blogs on my mind, lack the time, though): I think most of the DBAs have stumbled upon “how to fix orphaned users in SQL Server”, right? Well, a quick overview of the script:

SET
NOCOUNT
ON


SET
QUOTED_IDENTIFIER
OFF


GO

USE
<database name>


GO

DECLARE @sql varchar(100);

DECLARE curSQL CURSOR
FOR

SELECT
EXEC
sp_change_users_login
UPDATE_ONE, “‘+ name + “‘, “‘
+ name +
“”

FROM
sysusers

WHERE issqluser = 1


AND
suser_sname(sid)
IS
NULL


AND name NOT
IN
(dbo,guest)

OPEN curSQL

FETCH curSQL INTO @sql

WHILE
@@FETCH_STATUS
= 0 BEGIN

EXEC (@sql)

PRINT @sql

FETCH curSQL INTO @sql

END

CLOSE curSQL

DEALLOCATE curSQL


GO

So far, so good. Or is it?

Let’s have a very quick look on what’s under the hood, and for that we’ll actually query the sysusers table.

select
uid, name, sid, issqluser from
sysusers
:

and here’s the print screen from my test machine:

Now, please bear in mind that I don’t want to point anything else, BUT: you can (and actually will) have SQL users that are… well, not SQL Users (that is “issqluser – is set to 0) J. This happens for users associated to logins that are mapped to Windows accounts/groups. I hope this helps.

Best regards,
io

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: