Problem with SQL ID mapping on 2005 db move 2

[Ovatvvon]

I moved a database from a Dev to a QA server. The ID associated with the database is still present, and is a schema owner (owns all the tables). I tried to add the user to the SQL Server instance, which it does, however it doesn't map the same id from the server to the specific database. Instead, if I try to specify access (read/write) to the database for that ID, it gives me an error stating that the ID already exists. I cannot seem to be able to grant read/write access for the ID in the database.

Does anyone know what I may be doing wrong?


-Ovatvvon :-Q

 

[SQLBill]

You need to drop it from the database. Then create the login on the instance. Then assign it to the database. What you have is an orphaned user.

In 2005, this will mean you have to remove it from all of its schema ownerships.

-SQLBill

Posting advice: FAQ481-4875

 

[Ovatvvon]

is there an easy / quick way to remove it from all its schema ownerships? (Why is it that SQL Server will not just assign DBO as the schema owner to replace a user if you are deleting it?)


-Ovatvvon :-Q

 

[Jamfool]

ALTER AUTHORIZATION ON SCHEMA::[yourschemaname] TO [dbo]

 

[ptheriault]

run sp_change_users_login 'REPORT'
This will show you orphaned logins.
Then run
sp_change_users_login 'update_one', 'login', 'login'

It will correct user logins with mismatching SIDS.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ovatvvon]

The weird thing is, I tried both of these things above, one at a time, and it didn't want to let the SQL ID log in after either one of them. I kept running them (2 or 3 times), and it stayed the same. Then I came back around 45 minutes later, and everything seemed to be working. That's a bit confusing to me.

Either way, it is working now; although I'm not sure which one did the trick (or if it was a combination of both). Jamfool, when I ran your code, it doesn't look like it actually changed the owner of the objects (i.e. Tables still have that user listed as the owner). Ptheriault, yours mapped the ID's very nicely!

Thank you both for your help!


-Ovatvvon :-Q

 

[ptheriault]

sp_change_users_login will only map SIDs. I can't change user permissions. But, you can't modify an account until the sids match. If you refresh a database with a database from another server you will always have to run this to match user sids back up. I actually create all my logins on one server, then use sp_help_revlogins to script them. I then use my script to create the logins on all the other servers the user needs access to. By creating my logins this way I will never have the problem of mis-matching sids when I restore from one server to another.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ovatvvon]

Very good info Paul. Thanks! *Virtual Star*

-Ovatvvon :-Q