User Tools

Site Tools


tech:rdbms:sp_change_users_login

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.

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”?

Answer
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>
go
exec sp_change_users_login 'Auto_Fix', '<USER>',NULL,'<PASSWORD>'
go
  • The following code block associates the “User” in the database to an existing “Login”.
use <DBNAME>
go
exec sp_change_users_login 'Update_one', '<USER>','<LOGIN>'
go
tech/rdbms/sp_change_users_login.txt · Last modified: 2024/02/21 19:09 by 127.0.0.1

Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki