Drop and add users in multiple DBs

Question

Drop and add users in multiple DBs

Scott Hughes

SSC Journeyman

Points: 86
More actions
September 21, 2005 at 4:24 am

#89289

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 9 posts - 1 through 8 (of 8 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Scott,

do I understand correctly that you would like to "either drop or add a specific user (which will be the DBO)" I mean, you would like to change a DBO on a database for many databases?

I normally would use the following after moving the database:

Use MyDatabase

sp_changedbowner 'UserName'

I would use 'sa' for 'UserName' but you may use your 'NewUser'

Regards,Yelena Varsha

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 2

I like Yelena's solution, simple and easy. The adduser/drop user gets complicated, as you'll see in EM if you restore a db. Sometimes things don't synch right.

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

Hi Yelena

Thanks for taking the time out to reply.

I have about 60 databases that are included in the Log shipping scenario.

However my thinking was to try and automate this as much as possible.

What I would like to do is drop the user and then re-add it. However in some of the databases the username is OLDSERVER\NewUser. It is really these users that I would like to drop and add back in as just NewUser with DBO rights.

Ultimately I want all the databases to have the same user and to be assigned to the new windows login on the Secondary server

For the rest of the databases I suppose I could use the sp_change_users_login procedure to realign the user with the Windows Login but I was looking for a way that was as automated as possible - so that if there was a disaster even the cleaner could get our system back online

Regards

Scott

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Scott,

I was trying to understand what users you are talking about: Windows (Machnename\Username) or SQL Server standard logins?

For SQL Server standard logins I create a login on the secondary server with the same SID as on the primary server using sp_addlogin SID parameter. I use a cursor that would get all SIDs from sysusers from a primary server and creates an sp_addlogin statements for the new server. You do it once and then all future database moves will go well and the users from primary server will be recognized on a secondary server.

I sometimes do the following (you have to be at least on the intermediate level to do that) The script changes server option "Allow Direct updates"

Use Master

Exec sp_configure 'allow updates', 1

RECONFIGURE WITH OVERRIDE

GO

Then I reset SID in sysusers based on SID in syslogins on the secondary server then run

--This part will disallow updates to system tables

Use Master

Exec sp_configure 'allow updates', 0

RECONFIGURE WITH OVERRIDE

Go

This will work ONLY if as you say those users have DBO rights (or other common rights or be members of the same role) because individual rights in the database may be lost if you change SID.

DISCLAMER: You have to know your application and the database really well to understand if you can do that at all, things that are tracked by user's SID may break. Microsoft does not support direct updates to the system tables.

Regards,Yelena Varsha

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

Once again many thanks for your response Yelena.

I try and keep my SQL Server security model simple and as secure as possible. I do not use SQL Server authentication only Windows.

I have a single windows login that has dbo access to all of the user defined databases (these databases when online will be accessible to our web application).

Kind Regards

Scott

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 6

Hi Scott,

seems to me you are using local accounts on each server. Do you have a nt- or ads-domain? if so, you might use a domain account, which would exist on both servers (if they are members of the same domain), and no change of logins would be needed..

regards karl

Best regards
karl

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 7

regarding the problem with your script:

you need to put all sql-commands into 1 string and execute that, since the "use @dbname" is only valid for the scope of the exec...

karl

Best regards
karl

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

Many thanks for all your responses. This started of as a simple task to try and make life easier - when in fact I think I am making things much more complicated than what they really need to be.

We are in the process of deploying AD so this will make life easier.

I think I will go back to the primary db server and make everything consistent with regards to the users - and then generate a simple sp_change_users_login procedure to re-align the users on the secondary server.

Anything for an easy and painless life.

Kind regards

Scott