troubleshooting Question

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

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
5 Comments1 Solution56 ViewsLast Modified:
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.
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros