Take as an example the following screenshot:
As you can see, the SQL login “testLogin” has been assigned the database role “db_owner” on the database “SampleDB1” but along with that, “testLogin” was also set as the default schema.
If you do that, any object the login “testLogin” creates, their owner schema will be “testLogin” and not “dbo”.
If however, for any reason you want to remove “testLogin” from the SQL Server instance you will not be able to do it because the login, through its schema, will own database objects.
If you still want to remove the login don’t lose hope, there is a way! :)
For achieving the above you need to transfer back the ownership of the objects to the “dbo” (or any other) schema.
You can do that using the “ALTER SCHEMA” command. As described in BOL, this command transfers a securable between schemas.
So how can you use this command in order to transfer back to dbo all objects owned by "testLogin"?
One of the easiest approaches is to generate the set of SQL statements that you can run in order to restore ownership for all objects of the following types:
- functions (scalar, table-valued)
- stored procedures
- ...and maybe any other object types