October 8, 2010 at 12:08 pm
We are migrating from SQL 2000 to SQL 2008 on a new server. Originally we used SQL logins but are moving to Windows logins on all the new servers. In one of our databases all the tables, sp, etc are owned by a user login and not dbo. I thought the sp_change_users_login command would work for this but it doesn't work with windows domain accounts from what I've read.
So is there anyway to associate the SQL login johndoe with XXX\johndoe?
Or will I have to
1) change ownership of all objects to dbo
2) drop the johndoe user from the database
3) add XXX\johndoe as user johndoe
4) change ownership of all objects back to johndoe
October 8, 2010 at 12:26 pm
You are in a new world in 2008. I would advise you to read up heavily on schemas in BOL. Basically the object is no longer "owned" by a user, it belongs to a schema which is then assigned to a user. You should be able to reassign the schema after the upgrade to a different AD-based user. It should be a fairly quick and painless process.
October 8, 2010 at 1:58 pm
Thank you Jeff. I reassigned the schema then deleted the user of the same name. Then added the WA login to that database and reassigned the schema to the WA login of the same name.
Yes I definitely need to research schemas. For now I'm just migrating SQL 2000 databases straight over to 2008 and not break any applications in the process.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply