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
SQL-PLAY 