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.
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I have a situation where I am copying 10 databases from one instance of SQL to another.
are you using backup/restore or drop database / attach database ?
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. Why do you think that you have to do this?
are you using backup/restore or drop database / attach database ?
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. Why do you think that you have to do this?
so restore the backups
Why do you have to delete the users and recreate them? not using active directory users but local users?
Why do you have to delete the users and recreate them? not using active directory users but local users?
You do not need to drop local users. And, in fact, it would be a real pain to do that, because of course you'd lose all their permissions and you have to deal with their owned schema and owned objects. (Capturing and) Putting back all permissions can be a pain if they've been granted individual permissions.
Taking a step back to get a broader look at the underlying issue, SQL links a login to a user using the SID. Meaning, that by far the easiest way to deal with the restore issue is to create the logins on the new instance with the same SID as those logins had on the original instance. Then, the user will automatically sync to its login, the same way it does if you restore a db onto its original server.
1) On the new instance, drop the login(s) (even if corresponding users already exist in a few dbs, we can correct that later). Fortunately, deleting the login does not delete the users.
2) On the old instance, get the SID for the login(s) you want to copy.
SELECT * FROM sys.server_principals WHERE name IN ('user1')
You'll get output in a format like this, although of course the specific data will be different:
name principal_id sid
user1 1220 xA1D7D7AE39746947896BE9024
3) On the new instance, create the login with the same SID:
CREATE LOGIN [user1] WITH PASSWORD = '...', SID = xA1D7D7AE39746947896BE9024
After that, when you restore a db from the original instance, the user should automatically have all its old permissions, without any added steps.
For existing dbs with that user in it, at the time you create the login with the dup SID, you'd have to go thru them and do a one time sync of the old sid to the new sid, like so:
USE FUDD;
EXEC sp_change_users_login 'UPDATE_ONE', 'user1', 'user1'
If you prefer, you could always create new logins without specifying a SID, like you already have, but then you'll have to resync all users on every restored db with the command above.
You have your choice. I generally prefer to sync the SIDs, unless there's a clear security reason for not doing so, because then the restores just work, with no extra steps.
Taking a step back to get a broader look at the underlying issue, SQL links a login to a user using the SID. Meaning, that by far the easiest way to deal with the restore issue is to create the logins on the new instance with the same SID as those logins had on the original instance. Then, the user will automatically sync to its login, the same way it does if you restore a db onto its original server.
1) On the new instance, drop the login(s) (even if corresponding users already exist in a few dbs, we can correct that later). Fortunately, deleting the login does not delete the users.
2) On the old instance, get the SID for the login(s) you want to copy.
SELECT * FROM sys.server_principals WHERE name IN ('user1')
You'll get output in a format like this, although of course the specific data will be different:
name principal_id sid
user1 1220 xA1D7D7AE39746947896BE9024
3) On the new instance, create the login with the same SID:
CREATE LOGIN [user1] WITH PASSWORD = '...', SID = xA1D7D7AE39746947896BE9024
After that, when you restore a db from the original instance, the user should automatically have all its old permissions, without any added steps.
For existing dbs with that user in it, at the time you create the login with the dup SID, you'd have to go thru them and do a one time sync of the old sid to the new sid, like so:
USE FUDD;
EXEC sp_change_users_login 'UPDATE_ONE', 'user1', 'user1'
If you prefer, you could always create new logins without specifying a SID, like you already have, but then you'll have to resync all users on every restored db with the command above.
You have your choice. I generally prefer to sync the SIDs, unless there's a clear security reason for not doing so, because then the restores just work, with no extra steps.
Thanks, this will help. I do have one complicating issue and that is there is a solution installed keeps it own user database and sometimes when I copy it gets out of sync with SQL so a user in this application's user list does not exist in SQL and I start getting unexpected messages. I need to contact Experts-Exchange support as I am no longer notifications when solutions are submitted for my questions.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial