This was an interesting question I saw posted recently. Through SQL Server 2000, trying to delete a user that owned objects was a pain. We had to actually rebuild all the objects, which was problematic. With SQL Server 2005 and later, we got schemas actually added as separate entities, so that solves some of the issue.
To allow a user to be removed from a database when they own a schema, we need to move the ownership, or authorization of the schema to a new user.
Here’s a short repro of what to do.
Let’s say that I have a user in my database called SallyDev. This user is a DDL_admin and can create objects. I’ll add Sally to my database with this script:
CREATE LOGIN SallyDev WITH PASSWORD ='SomethingStrong'
GO
CREATE USER SallyDev FOR LOGIN SallyDev
GO
ALTER ROLE db_ddladmin ADD MEMBER SallyDev
GO
As such. SallyDev has run this script:
CREATE SCHEMA SallyDev
GO
CREATE TABLE SallyDev.Class
( ClassKey INT IDENTITY(1,1)
, ClassName VARCHAR(100)
, Active BIT
)
Now, SallyDev has left the team and we need to remove her user and login. When we try to remove the user, we get this error:
In SQL 2000, Sally would have owned the table and we would have gotten a different message. Now, Sally owns the schema, but the schema now owns the table.
To fix this, we need a new owner for the schema. We can use any user with the ALTER AUTHRORIZATION command like this:
ALTER AUTHORIZATION ON SCHEMA::SallyDev TO JoeDev
I don’t recommend another user as we are just deferring the same problem. Instead, I’d move the schema to dbo.
ALTER AUTHORIZATION ON SCHEMA::SallyDev TO dbo
Once this is complete, we can drop the SallyDev user.
We can also verify the schema has moved to a new user.
Voice of the DBA 