Not sure who was the original author of this short synchronization script but kudos to whomever it is!
go
/* delete names not found in master syslogins */
DELETE FROM sysusers
WHERE suid > 1
and uid < =16383
and uid > 1
and name not in
(SELECT name FROM master..syslogins)
go
/* update sysusers and resync all uids from the existing suids */
IF exists(SELECT 1
FROM sysobjects
WHERE name = "sysusers_holding"
and type = "U")
DROP TABLE sysusers_holding
go
CREATE TABLE sysusers_holding (
id NUMERIC(6,0) IDENTITY PRIMARY KEY,
suid INT null,
uid INT null,
gid INT null,
name CHAR(30) null,
environ VARCHAR(255) null)
go
/* populate the holding table */
INSERT INTO sysusers_holding (suid, gid, name, environ)
SELECT suid, gid, name, environ
FROM sysusers
WHERE suid > 1
and uid < = 16383
and uid > 1
go
/* update the uids. */
DECLARE syncuser CURSOR FOR
SELECT name, uid
FROM sysusers_holding
FOR UPDATE OF uid, suid
go
DECLARE @name CHAR(30), @uid INT, @suid INT
SELECT @suid = 9999
OPEN syncuser
FETCH syncuser INTO @name, @uid
WHILE (@@sqlstatus != 2)
BEGIN
IF exists(SELECT 1
FROM master..syslogins
WHERE name = @name)
/* got the name - update the uid */
BEGIN
SELECT @suid = suid
FROM master..syslogins
WHERE name = @name
UPDATE sysusers_holding SET uid = @suid, suid = @suid WHERE CURRENT OF syncuser
IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
BREAK
END
SELECT @suid = 9999
END
FETCH syncuser INTO @name, @uid
END
CLOSE syncuser
go
DEALLOCATE CURSOR syncuser
go
/* now move records over */
DELETE FROM sysusers
WHERE suid > 1
and uid < =16383
and uid > 1
go
INSERT INTO sysusers
SELECT suid, uid, gid, name, environ
FROM sysusers_holding
go
/* sp_helpuser will show any mis-matches) */
EXEC SP_HELPUSER
go
EXEC SP_CONFIGURE "allow updates", 0
go
Listen to this podcast
Recent Comments