.Net ramblings
# 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)  #    Comments [2]  Database