When you have a SQL Server Database with several dozen accounts, you want to transfer it to another server, the problem you encounter is that many sql accounts have previously been granted certain rights on each object such as table, store procedure, view, function … and you can’t remember which user is holding which permission … to reassign it.
Many people wonder about the process Detach and attach Database losing the rights of the user on it, the following content will guide you how to retain all the rights on that Database without having to create a new account and reassign the rights to it.
How to Attach and Detach database does not lose user rights in SQL Server
Step 1: Detach and attach Database normal
2.1 Write down the names of the users who are in the medium database attach up
2.2 Go to Sercurit: Logins to recreate the users just noted above
Step 3. After creating the account with the same name, proceed Map the rights of each database user:
Run the SQL command: EXEC sp_change_users_login ‘Update_One’, ‘UserName_Need_Runner’, ‘UserName_Need_Runner’
For example: EXEC sp_change_users_login ‘Update_One’, ‘New1’, ‘New1’
sp_change_users_login is a system procedure available in SQL that will help us regain database permissions. without having to assign individual permissions on each user is complicated.
SQL Server is very important for companies and businesses, so backing up and restoring SQL Server is very important, with the SQL Server backup and recovery tutorial that we shared will help you. You can back up your data and restore it in the worst case scenario, the time taken for SQL Server backup depends on your data capacity.