Cannot drop user who is a Schema owner

Question

Cannot drop user who is a Schema owner

tt-615680

SSCertifiable

Points: 7835
More actions
September 7, 2018 at 9:24 am

#362441

Dear All,
I'm currently trying to remove a user who is no longer required to be on the server but I'm receiving the following message "There is a schema that has the same name as the user you are deleting; is it possible to rename the schema or would I have to recreate it completely?
Thank you in advance!
September 19, 2018 at 2:56 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

tt-615680 - Friday, September 7, 2018 9:24 AM
Dear All,
I'm currently trying to remove a user who is no longer required to be on the server but I'm receiving the following message "There is a schema that has the same name as the user you are deleting; is it possible to rename the schema or would I have to recreate it completely?
Thank you in advance!

You can't rename a schema so that's not an option.
If it's an empty schema and isn't the default for any users you could drop the schema.
If that's not the case , another option is to rename the user in the database. How you do that depends on if it's a windows or sql account but generally it's just something like:
USE YourDatabase GO ALTER USER <UserName> WITH NAME = <NewUserName> GO
And then you could drop the user without the warning.
If there are objects in the schema, you can transfer them to another schema but references to those objects would be different if using two part naming.

Sue

Smendle Hall of Fame Points: 3969 More actions · Answer 2

tt-615680 - Friday, September 7, 2018 9:24 AM
Dear All,
I'm currently trying to remove a user who is no longer required to be on the server but I'm receiving the following message "There is a schema that has the same name as the user you are deleting; is it possible to rename the schema or would I have to recreate it completely?
Thank you in advance!

Couldnt you just change ownership of the schema to another DB user or sa and then drop the user?

ALTER AUTHORIZATION ON SCHEMA::[SCHEMANAME] TO [USERNAME]

MSDN Documentation on ALTER AUTHORIZATION

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 734564

September 10, 2018 at 8:07 am

#2005081

Moving the authorization to a new user works.

tt-615680 SSCertifiable Points: 7835 More actions · Answer 4

Steve Jones - SSC Editor - Monday, September 10, 2018 8:07 AM
Moving the authorization to a new user works.

Thank you for the reply! I've changed the owner of the schema; but the issue seems to be with the actual name of the schema where it is named as a particular user which is the one I'm trying to remove.

Thank you

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 5

If you want to rename the schema, you have to essentially drop it and recreate it. However, in your case, with no need to worry about which schema, what I'd do is create a new schema, then use ALTER SCHEMA ... TRANSFER to move the objects from the old user schema to the new one.