A Mind Lost

As mentioned in earlier posts, as time permits I’m migrating an old SQL Server 7 installation over to a new SQL Server 2005 installation.  One of the things I came across was that even though I had Users, Roles and Permissions set up an looking OK, I was unable to run even a simple SELECT query against the database without an error stating that the user didn’t have permission.  After checking all of the users/roles/permissions for the database and its tables and finding nothing wrong, I noticed that the properties for the user did not show a login name and any attempt to change the properties would demand a valid login name even though the field was greyed out and not editable!

The problem appears to be that when restoring an older database into SQL Server 2005, the login name can be assigned a blank entry.  The easy fix if your user names map to logins of the same name, i.e. login user1 maps to user user1, is to run the following query for each database user:

USE [database_name];
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘Username’;
GO

You should get the following response:

The row for user ‘Username’ will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

More info can be found here.