User Tools

Site Tools


SQL Server - "Managing database users and logins"

In Microsoft SQL Server there are two forms of “User ID's”:

  1. There is a SQL Server “Login” which aloows the user to connect to the SQL Server. This data is usually found under the “Security” folder.
  2. Under the Databases –> <DBNAME> –> Security –> Users folder are the users that are allowed to access the database in question.

What happens when you are restoring a foreign database into your SQL Server. The Database has a set of users allowed to access it but there is no corresponding SQL Server “Login”?

This is where sp_change_users_login comes into play. There are two main ways to use the stored procedure:

  • The following code block shows you how to automatically create a “Login” for the “User” in your recently restored database.
use <DBNAME>
exec sp_change_users_login 'Auto_Fix', '<USER>',NULL,'<PASSWORD>'
  • The following code block associates the “User” in the database to an existing “Login”.
use <DBNAME>
exec sp_change_users_login 'Update_one', '<USER>','<LOGIN>'
tech/rdbms/sp_change_users_login.txt · Last modified: 2024/06/21 12:04 by