Troubleshoot disconnected SQL Server users

July 3, 2007   12:15

If you move a database export to a new server, you might experience login problems inside your applications.

The exported database will contain your users, but they are no longer connected to valid SQL Server logins. Even if the same users are existing on the new SQL Server, their ID’s might be different. One of the options is to just recreate and configure the users, but sometimes this is not an option; You might have a lot of users or complicated user rights that you don’t want to check, test and document.

SQL Server contains stored procedure in the Master database to connect your database users with the available SQL Server users. This stored procedure is called “sp_change_users_login”. Just take the steps below to attach your users:

1. Create the (new) required login in the Security database if it doesn’t exist.
2. Run the following SQL and replace the coloured items with your values:
   USE [Your database]
   go
   EXEC sp_change_users_login ‘Update_One’, ‘Original username‘, ‘SQL Server user name‘

Narayana Vyas Kondreddi has written more detailed information about this subject at his website which contains a handy stored procedure “ShowOrphanUsers” which provides you with a list of all unconnected users.

Documentation about the “sp_change_users_login” stored procedure can be found at the MSDN website.

Posted in SQL Server | Featured url | Permalink | Post first comment

No Comments »

No comments yet.

RSS feed for comments on this post.

Leave a comment