Deleting a User that Owns a Schema

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:

2018-09-15 01_02_05-SQLQuery1.sql - dkrSpectre_sql2017.sandbox (DKRSPECTRE_way0u (68))_ - Microsoft

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.

2018-09-15 01_06_37-Schema Properties - SallyDev

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

One Response to Deleting a User that Owns a Schema

  1. Pingback: Moving Objects to a New Schema | Voice of the DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.