Sunday, 11 January 2009
SQL 2005 cannot drop user with owned schema
i notice when i backup a database and restore it to another server, the user IDs are different and i have to delete the users and add them back in to the database. however last time i tried this the drop failed because the user owned a schema. i right-clicked on the user's properties and the tick box for 'db_owner' was shaded in a read only state. so i couldn't delete the user and couldn't remove their schema ownership. took a while to figure out that the way to do this is via the Database > Security > Schemas menu, then change the schema owner back to itself, e.g. db_owner. then the user is free to be deleted.
Sunday, 11 January 2009 15:28:04 (GMT Standard Time, UTC+00:00) Database