Technical Article

No more orphan users!

,

I was fed up of manipulating sysusers after restoring a db from one server to another.
Just put thgis script on QA after restoring db. It will check all db users (not tested for secure connections) and will restore orphan users. Check it out!
Hope this script will help.

/*
Philippe RUELLO - 30/01/03
This Proc verify that all db users have the same sid than those in db master
You must have Admin rights on server
*/
USE master
--Allow sys updates
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
SET NOCOUNT ON
DECLARE CurdB CURSOR FOR SELECT [name] from master.dbo.sysdatabases WITH (NOLOCK) WHERE (master.dbo.sysdatabases.dbid > 4 ) AND (status<=16)     --(= base utilisateur active)
DECLARE @c nvarchar(500), @Name as nvarchar(300), @sid as varbinary(85), @sidc as varchar(85), @sidMaster as varbinary(85)
DECLARE @nom as sysname, @logindb nvarchar(132), @loginlang nvarchar(132)

--Openning cursor for the user db
OPEN CurdB
FETCH NEXT FROM CurdB into @Name
--all the db users are tested (except dbo)
WHILE @@FETCH_STATUS = 0 
BEGIN
print '--- Verifying base: ['+@Name +'] ...'
SET @Name = '['+@Name+']'
--declare and open cursor on db users for the current db
SET @c = 'DECLARE CurUsr CURSOR FOR SELECT [name],[sid] from ' + @Name + '..[sysusers] WITH (NOLOCK) WHERE [status]=''2'' AND [issqluser]=''1'''
EXEC sp_executesql @c
OPEN CurUsr
FETCH NEXT FROM CurUsr into @nom, @sid
WHILE @@FETCH_STATUS = 0 
BEGIN
--verifying user exists in master db
IF EXISTS (SELECT sid FROM master..sysxlogins WITH (NOLOCK) WHERE name=@nom) 
BEGIN
SELECT @sidMaster = sid FROM master..sysxlogins WITH (NOLOCK) WHERE name=@nom
EXEC master..xp_varbintohexstr @sidMaster, @sidc out
--if sid db user<> sid  db master then I put the sid db matser in the sid db user
IF (@sid <>@sidMaster) AND (@nom NOT LIKE '%dbo%') BEGIN
SET @c = 'UPDATE '+@Name+'..[sysusers] SET sid='+@sidc+' WHERE name='''+@nom+''''
EXEC sp_executesql @c
print '      Database: '+@Name +' - Nom utilisateur: '''+ @nom+''' - Affectation du sid: '+ @sidc+' depuis la base master'
END ELSE BEGIN
--sid correct
IF (@nom NOT LIKE '%dbo%') BEGIN
print '      L''utilisateur '''+@nom+''' a un sid correct ('+@sidc+')'
END
END
END ELSE BEGIN
--if user doesn't exist, create it with passwd=login
IF (@nom NOT LIKE '%dbo%') BEGIN
SET @loginlang = 'Français'
SET @logindb = @Name
exec sp_addlogin @nom, @nom, @logindb, @loginlang
SELECT @sidMaster = sid FROM master..sysxlogins WITH (NOLOCK) WHERE name=@nom
EXEC master..xp_varbintohexstr @sidMaster, @sidc out
SET @c = 'UPDATE '+@Name+'..[sysusers] SET sid='+@sidc+' WHERE name='''+@nom+''''
EXEC sp_executesql @c
print '      Database: '+@Name +' - Utilisateur créé: '''+ @nom+''', mot de passe:'''+ @nom +''' - Affectation du sid: '+ @sidc+' depuis la base master'
END
END
FETCH NEXT FROM CurUsr into @nom, @sid
END
CLOSE CurUsr
DEALLOCATE CurUsr
FETCH NEXT FROM CurdB into @Name
END
CLOSE CurdB
DEALLOCATE CurdB
GO
--Disallow sys updates
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating