Preserving User Permissions during MS SQL Server Database Attach and Detach

Buzz

Ngày cập nhật gần nhất: 15/4/2026

When migrating a SQL Server database to a new server, maintaining user permissions can be daunting. With numerous accounts assigned specific permissions on various objects such as tables, stored procedures, views, and functions, recalling each user's permissions becomes cumbersome. Here's how to seamlessly transfer the database without losing any permissions.

Many wonder whether Detaching and Attaching a database in SQL Server will strip away user permissions. The following steps guide you on how to retain all permissions on the database without creating new accounts or reassigning permissions.

Preserve User Permissions when Attaching and Detaching Databases in SQL Server

Step 1: Detach and attach the Database as usual.
Step 2:
2.1 Record the names of the Users present in the attached Database.

2.2 Navigate to Security: Logins to recreate the users recorded above.

Step 3. Once the accounts with matching names are created, proceed to remap permissions for each user of the Database:

Execute SQL command: EXEC sp_change_users_login 'Update_One', 'Username_to_be_Remapped', 'Username_to_be_Remapped'

Example: EXEC sp_change_users_login 'Update_One', 'New_User', 'New_User'

sp_change_users_login is a built-in system procedure in SQL that helps retrieve permissions for databases without the complexity of individually assigning permissions to each user.

SQL Server holds significant importance for companies and enterprises. Therefore, backing up and restoring SQL Server is crucial. Our guide on SQL Server backup and restore will assist you in independently backing up your data and restoring it in adverse situations. The backup time of SQL Server depends on the data size.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]