We help IT Professionals succeed at work.
Get Started

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

rwheeler23
rwheeler23 asked
on
53 Views
Last Modified: 2019-12-05
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.
USE 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.
Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE