Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
itsonlyme4
Posting Yak Master
109 Posts |
 Posted - 2008-11-10 : 09:35:30
|
| I am in the procecss of migrating a Database from SQL Server 2000 to 2005. In the past I have logged into my SQL Server 2000 Standard Edition Instance, scripted out the users associated with the database that I want to migrate then recreate the users on the 2005 Instance. Then when I restore my 2000 DB to 2005, all users and their permissions are already in place. This time I have a little bit of a different situation. The DB I want to move needs ALOT of clean-up!!! Seems the previous DBA made every NT authenticated login that has access to this database OWNER of the Database. What I WANTED to do in my test system is restore the database (2000 to 2005) then in the 2005 DB, drop all of the users so that I can create a DB role and add each user to the Role for their permissions.My problem is, I cannot drop the users from my 2005 DB because I get this errror: Msg 15138, Level 16, State 1, Line 3The database principal owns a schema in the database, and cannot be dropped.I know I'm getting this error because the user is designated as DB_Owner.How can I get around this and drop the user? Is there a better way of doing this migration??? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-10 : 11:41:00
|
| why don't you cleanup in primary database and transfer users to other server,map DB users with login with sp_change_users_login 'update_one'? |
 |
|
|
|
|
|
drop db_owner(s)