Avatar of rwheeler23
Flag for United States of America

asked on 

Dropping users from MSQL databases and then adding them back after server move.

I have a situation where I am copying 10 databases from one instance of SQL to another. I also have 50+ users to worry about. Usually I have no issues but with this set I have found that I need to go to each database and delete each user from the database then use an application to grant access back to the user per company. For some users the deletion does not work because I will get a message about the schema being owned by the user. There is also a database role called DYNGRP. I am looking to create a script that will go through the user list and delete all users from each database and then map the users back to each company and well as make them members of the DYNGRP database role per database. Which T-SQL commands will I need to accomplish this?

DROP USER will drop the user from each database
dbo should be the default schema for all users mapped
ALTER ROLE can be used to change membership

So let's say I have a user called ELMER and a database called FUDD.
DROP USER ELMER will drop the user but what about when I get the message about the user owning the schema?
After the user has been dropped how do I then add the database mapping back to this user to this database as well as make them members of the database role.
I am looking guidance as to the correct T-SQL commands to use.
I believe I am running into this because the source instance of SQL is 15+ years old and who knows what people have been doing in there. I am her to clean this mess up.

Avatar of undefined
Last Comment

8/22/2022 - Mon