MS SQL 2K thinks user is both there and NOT there... what gives??

Hi all,
I've restored a database, and MS SQL is giving me conflicting messages
on whether my main login user is there or not.
My main user - padmin - on the old DB was setup as owner, and padmin
is listed under Logins. But when I select my database > Users then
select padmin and check db_owner and OK, I get this message:
Error 15410: User or roll 'padmin' does not exist in this database.
Uhh, it's there, I'm looking at it. When I click on Permissions
button in the same window, I get this message:
Error 15001: Object '(null)' does not exist or is not a valid object
for this operation.
So... I goto Security > Logins and select padmin (the user SQL says
doesn't exist) and manually permit it to have access and db_owner role
of my database. I get this error:
Error 21002: [SQL-DMO] User 'padmin' already exists.
When I try to delete padmin, I get this error:
Error 15174: Login 'padmin' owns one or more database(s). Change the
owner of the following database(s) before dropping login... and it
lists my database.
So, from within my database, padmin doesn't exist and isn't the owner,
but to MS SQL 2000 it does exist and IS the owner.
What gives? How can I get past this? I have programs and many ODBC
connections that use padmin, so I can't simply create another user and
use this. I'm guessing this is nothing more then a bug in MS SQL, but
I have no idea how to resolve it.
Thanks for any ides, solutions, or suggestions... and take care,
Alex.Hi Alex,
You can fix this with the system stored procedure sp_change_users_login.
Try:
EXEC sp_change_users_login 'AutoFix', 'padmin' from the database.
--
Jacco Schalkwijk
SQL Server MVP
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0401290903.34126c7c@.posting.google.com...
> Hi all,
> I've restored a database, and MS SQL is giving me conflicting messages
> on whether my main login user is there or not.
> My main user - padmin - on the old DB was setup as owner, and padmin
> is listed under Logins. But when I select my database > Users then
> select padmin and check db_owner and OK, I get this message:
> Error 15410: User or roll 'padmin' does not exist in this database.
> Uhh, it's there, I'm looking at it. When I click on Permissions
> button in the same window, I get this message:
> Error 15001: Object '(null)' does not exist or is not a valid object
> for this operation.
> So... I goto Security > Logins and select padmin (the user SQL says
> doesn't exist) and manually permit it to have access and db_owner role
> of my database. I get this error:
> Error 21002: [SQL-DMO] User 'padmin' already exists.
> When I try to delete padmin, I get this error:
> Error 15174: Login 'padmin' owns one or more database(s). Change the
> owner of the following database(s) before dropping login... and it
> lists my database.
> So, from within my database, padmin doesn't exist and isn't the owner,
> but to MS SQL 2000 it does exist and IS the owner.
> What gives? How can I get past this? I have programs and many ODBC
> connections that use padmin, so I can't simply create another user and
> use this. I'm guessing this is nothing more then a bug in MS SQL, but
> I have no idea how to resolve it.
> Thanks for any ides, solutions, or suggestions... and take care,
> Alex.|||try sp_change_users_login 'update_one', 'padmin', 'padmin' to fix orphaned
user account after db restore.
richard
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0401290903.34126c7c@.posting.google.com...
> Hi all,
> I've restored a database, and MS SQL is giving me conflicting messages
> on whether my main login user is there or not.
> My main user - padmin - on the old DB was setup as owner, and padmin
> is listed under Logins. But when I select my database > Users then
> select padmin and check db_owner and OK, I get this message:
> Error 15410: User or roll 'padmin' does not exist in this database.
> Uhh, it's there, I'm looking at it. When I click on Permissions
> button in the same window, I get this message:
> Error 15001: Object '(null)' does not exist or is not a valid object
> for this operation.
> So... I goto Security > Logins and select padmin (the user SQL says
> doesn't exist) and manually permit it to have access and db_owner role
> of my database. I get this error:
> Error 21002: [SQL-DMO] User 'padmin' already exists.
> When I try to delete padmin, I get this error:
> Error 15174: Login 'padmin' owns one or more database(s). Change the
> owner of the following database(s) before dropping login... and it
> lists my database.
> So, from within my database, padmin doesn't exist and isn't the owner,
> but to MS SQL 2000 it does exist and IS the owner.
> What gives? How can I get past this? I have programs and many ODBC
> connections that use padmin, so I can't simply create another user and
> use this. I'm guessing this is nothing more then a bug in MS SQL, but
> I have no idea how to resolve it.
> Thanks for any ides, solutions, or suggestions... and take care,
> Alex.|||If you are still having problem tries this.
1. Common problem when the db is moved to another environment and GUI
is used to recreate the user ID.
2. First check sp_helpuser to see if it is present.
3. sp_dropuser to get clean out the error.
4. sp_adduser to give access/assignment.
When moving database from one relocation to another location
everything including the users should be moved because of the
relations they have as each user id and its environment have unique
keys/id.