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 🙂

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in How To, Just Learned, Security in SQL Server, SQL Server and tagged , , , . Bookmark the permalink.

2 Responses to User Mapping With ALTER USER

  1. blakhani says:

    Good refresher!
    Windows logins normally would not have such problem because SID does not change across machines within same domain.

  2. sudhir says:

    very useful post … sql dba

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s