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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.