Drop and add users in multiple DBs

Question

Post reply

Drop and add users in multiple DBs

Scott Hughes

SSC Journeyman

Points: 86
More actions
September 21, 2005 at 2:17 am

#89993

Hello
I am trying to prepare a script that will be used with my Log Shipping/DR process.
Once I have rought all Dbs online I would like to step through each database and either drop or add a specific user (which will be the DBO).
The problem that I have is that in some databases the dbo is MachineName\NewUser and others it is NewUser. I would like to drop all these occurences and recreate them so that the user in the db wil be NewUser.
However my logic seems to be a bit over the place as when I run the following script the user that is returned from the (select name from sysusers)appears to be coming from the database that you actually run the script in rather that taking it from the database in the cursor
DECLARE @dbname VARCHAR(250)
DECLARE @Adduser NVARCHAR(250)
DECLARE @Amenduser NVARCHAR(250)
DECLARE @loginname VARCHAR(250)
DECLARE @SQLString NVARCHAR(250)
DECLARE @SQLString1 NVARCHAR(250)
DECLARE databasename CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE dbid > 4
OPEN databasename
FETCH NEXT
FROM databasename
INTO @dbname

IF @@FETCH_STATUS <> 0
  PRINT 'No User Defined Databases Found'

WHILE @@FETCH_STATUS = 0
  BEGIN

   SET @sqlstring = 'USE ' + @dbname
    EXEC sp_executesql @sqlstring
   PRINT @dbname + ' ******active db name*****'
   SET @loginname = (SELECT name FROM sysusers WHERE name LIKE '%NewUser%')
   print @loginname + ' User name'
   IF (SELECT COUNT(@loginname)) <> 0
    BEGIN
     SET @Amenduser = 'EXEC SP_DROPUSER ' + char(39)+ @loginname + char(39) + + char(13) +
     'EXEC SP_ADDUSER ' + char(39) + 'MachineName\NewUser' + char(39)+ ','+ char(39)+'NewUser' + char(39)+ ',' + char(39)+ 'db_owner' + char(39)
     Print @amenduser + ' Drop and add'
     EXEC sp_executesql @amenduser
    END
   --  ELSE
    --BEGIN
    -- SET @Adduser = 'EXEC SP_ADDUSER ' + char(39) + 'MachineName\NewUser' + char(39)+ ','+ char(39)+'NewUser' + char(39)+ ',' + char(39)+ 'db_owner' + char(39)
    -- Print @adduser + ' Add user'
    -- EXEC sp_executesql @adduser
    --END

   FETCH NEXT FROM databasename INTO @dbname
  END
CLOSE databasename
DEALLOCATE databasename
GO

Any help would be grately appreciated as my mind is going round the bend....
Cheers
Scott

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sergi Langa SSC Enthusiast Points: 126 More actions · Answer 1

I have just a suggestion. Have you tried this?

Instead of

SET @sqlstring = 'USE ' + @dbname

EXEC sp_executesql @sqlstring

PRINT @dbname + ' ******active db name*****'

SET @loginname = (SELECT name FROM sysusers WHERE name LIKE '%NewUser%')

You could use

SET @sqlstring = ' SET @loginname = (SELECT name FROM ' + @dbname + 'dbo.sysusers WHERE name LIKE '%NewUser%') '

EXEC sp_executesql @sqlstring

vambati SSCrazy Points: 2769 More actions · Answer 2

This might be stupid, but last i heard was that some of the system tables are no longer in the master db. Just a chek.

cheers,

babu.

Scott Hughes SSC Journeyman Points: 86 More actions · Answer 3

Hello All

Many thanks for your responses. Unfortunately my issues are specific to SQL 2000 and not Yukon. I opened the thread in the wrong forum.

Once again thank you for your suggestions.

Doh!!!

Cheers

Scott