sp_change_users_login and ALTER USER

Crusty old SQL Server DBAs know all sorts of stored procedures for fixing up databases. A very useful one is sp_change_users_login, which you can use to map a database user to a server login after restoring a database from a backup.

You will need to do this when rebuilding a database server from scratch, because the backup does not store information about logins or any of the other server-level assets.

If you have to map the server login appuser to the SuperApp database user appuser, and you want to stay crusty, you can use

EXECUTE SuperApp.dbo.sp_change_users_login
  @Action = 'Auto_Fix',
  @UserNamePattern = N'appuser';

But you should prefer the new T-SQL language constructs, because the stored proc is going to be removed in a future version of SQL Server, and it is confisingly overloaded – it can change mapping or report on mapping depending on what value you pass to the @Action parameter.

The shiny new way top do the same is the ALTER USER statement:

USE DATABASE SuperApp;
ALTER USER appuser WITH LOGIN = appuser;
Advertisements

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