User Mapping With ALTER USER

Yesterday I was working with my colleague to help him with a user-mapping issue. He was migrating a database to SQL server 2008 R2 environment. He restored the database in the new environment from a recent backup and copied all the logins using the sp_help_revlogin stored procedure. Although the logins were present in the instance, the existing users were not mapped correctly and there were some orphaned users present in the database. 

We can fix this issue either with the help of the system stored procedure sp_change_users_login or with the ALTER USER command. Using the system stored procedure sp_change_users_login, we can fix the user-mapping issues; however the stored procedure will be deprecated in future version of SQL Server and is limited only to the SQL Login.
USE [SKG_Test];
GO
EXEC sp_change_users_login 'Update_One', 'skganguly', 'skganguly';
GO

So I asked him to use the ALTER USER command to fix the login issue, which can fix both the Windows Logins as well as SQL Login.
USE [SKG_Test];
GO
ALTER USER [skganguly] WITH LOGIN = [skganguly];
GO

In case you want to read more about the available options for these commands, click here and here.

Happy Learning

About these ads